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
----logic goes here----
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,
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
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
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 –
1 2013-04-05 06:26:00