Triggers

Triggers in SQLite database are the database callback functions which are invoked or performed whenever a specified database event occurs. Some of the important points regarding triggers are as followed –

• SQLite triggers may be triggered at the time when INSERT, DELETE or UPDATE query is fired on one or more specified columns of the table.
• At present SQLite supports triggers only for, FOR EACH ROW not for, FOR EACH STATEMENT. 
• The BEFORE and AFTER keyword in triggers are used to specify when the trigger action needs to be performed relative to insertion, modification or deletion of a specific row.
• A trigger automatically dropped whenever a table associated with that trigger is dropped.
• If an exception to be raised, use of an SQL function RAISE() is used. 

A trigger can be written in the following way –

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name

ON table_name

BEGIN

 ----logic goes here----

END;

Here, event_name can be anything such as INSERT, DELETE or UPDATE database operation as mention on the table_name. 

Let's take an example to understand triggers -

Let's create a table named COMPANY –

CREATE TABLE COMPANY(

   ID INT PRIMARY KEY NOT NULL,

   NAME TEXT NOT NULL,

   AGE INT NOT NULL,

   ADDRESS CHAR(50),

   SALARY REAL

);

To keep the trigger record we will create another table named AUDIT, such that whenever a new entry is inserted into the COMPANY table the log is recorded in the AUDIT table.

CREATE TABLE AUDIT(

   EMP_ID INT NOT NULL,

   ENTRY_DATE TEXT NOT NULL

);

We need to create the trigger for insert operation such that whenever the insertion is performed it records the value.

CREATE TRIGGER audit_log AFTER INSERT 

ON COMPANY

BEGIN

   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));

END;

Let’s insert some record in the COMPANY table –

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Paul', 32, 'California', 20000.00 );

Now the trigger will be fired and it records the value –

EMP_ID ENTRY_DATE

---------- -------------------

1 2013-04-05 06:26:00