Sqlite Trigger

SQLite Triggers

SQLite Triggers are database callback functions that are automatically executed/called when a specified database event occurs. Here are the key points about SQLite Triggers:

SQLite Triggers can be specified to fire on DELETE, INSERT, or UPDATE of a specific database table, or on the update of one or more specified columns of a table.
SQLite only supports FOR EACH ROW Triggers, not FOR EACH STATEMENT Triggers. Therefore, explicitly specifying FOR EACH ROW is optional.
The WHEN clause and the trigger action may access the row elements of the inserted, deleted, or updated row using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table associated with the trigger.
If a WHEN clause is provided, the SQL statements are executed only for the specified rows where the WHEN clause is true. If no WHEN clause is provided, the SQL statements are executed for all rows.
The BEFORE or AFTER keyword determines when the trigger action is executed, i.e., whether the trigger action is executed before or after the insertion, modification, or deletion of the associated row.
When the table associated with the trigger is deleted, the trigger is automatically deleted.
The table to be modified must exist in the same database as the table or view to which the trigger is attached, and must be referenced using only tablename, not database.tablename.
A special SQL function RAISE() can be used within a trigger program to raise an exception.

Syntax

The basic syntax for creating a Trigger is as follows:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name
BEGIN
    -- trigger logic.....
END;

Here, event_name can be the INSERT, DELETE, and UPDATE database operations on the mentioned table table_name. You can optionally specify FOR EACH ROW after the table name.

The syntax for creating a Trigger on one or more specified columns of a table for an UPDATE operation is as follows:

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name
BEGIN
    -- trigger logic.....
END;

Examples

Let's assume a situation where we want to maintain an audit trial for every record inserted into a newly created COMPANY table (if it already exists, it is deleted and recreated):

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To maintain an audit trial, we will create a new table called AUDIT. A log message will be inserted into it whenever a new record is inserted into the COMPANY table:

sqlite> CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

Here, ID is the AUDIT record ID, EMP_ID is the ID from the COMPANY table, and DATE will keep the timestamp when the record in COMPANY was created. So, let's now create a trigger on the COMPANY table as shown below:

sqlite> CREATE TRIGGER audit_log AFTER INSERT ON COMPANY
BEGIN
    INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Now, we will start inserting records into the COMPANY table, which will cause an audit log record to be created in the AUDIT table. So, let's create a record in the COMPANY table as shown below:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

This will create a record in the COMPANY table as follows:

ID          NAME        AGE        ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

Similarly, a record will be created in the AUDIT table. This record is the result of the trigger we created on the INSERT operation on the COMPANY table. Similarly, you can create triggers on UPDATE and DELETE operations as needed.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

List Triggers

You can list down all the triggers from the sqlite_master table as follows:

sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';

The above SQLite statement will list down only one entry as follows:

name
----------
audit_log

If you want to list down triggers on a specific table, then use AND clause along with the table name as follows:

sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';

The above SQLite statement will list down only one entry as follows:

name
----------
audit_log

Delete Triggers

Below is the DROP command, which can be used to delete an existing trigger:

sqlite> DROP TRIGGER trigger_name;

YouTip