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


Leave Comment