SQLite Trigger

SQLite Triggeris a callback function of the database, which is automatically executed/called when the specified database event occurs. The following 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 updates to columns of one or more specified tables.

  • SQLite only supports FOR EACH ROW triggers, and there are no FOR EACH STATEMENT triggers. Therefore, explicitly specifying FOR EACH ROW is optional.

  • The WHEN clause and trigger actions may access, using the formNEW.column-nameandOLD.column-namereferences to the row elements being inserted, deleted, or updated, 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 for which the WHEN clause is true. If no WHEN clause is provided, the SQL statements are executed for all rows.

  • The BEFORE or AFTER keywords determine when trigger actions are executed, deciding whether they run 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 only usetablename, notdatabase.tablename。

  • A special SQL function RAISE() can be used to raise an exception within the trigger program.

Syntax

CreateTriggerThe basic syntax is as follows:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

Here,event_namecan be an operation on the mentioned tabletable_nameon theINSERT, DELETE, and UPDATEdatabase operation. You can optionally specify FOR EACH ROW after the table name.

The following is the syntax for creating a trigger on one or more specified columns of a table for UPDATE operations:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

Examples

Let us 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, delete and recreate it):

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 the audit trial, we will create a new table named AUDIT. Whenever there is a new record entry in the COMPANY table, a log message will be inserted into it:

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

Here, ID is the ID of the AUDIT record, EMP_ID is the ID from the COMPANY table, and DATE will hold the timestamp when the record was created in COMPANY. So, now let us create a trigger on the COMPANY table as follows:

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. Therefore, let us create a record in the COMPANY table as follows:

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

This will create the following record in the COMPANY table:

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

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

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

Listing Triggers (TRIGGERS)

You can list all triggers from thesqlite_mastertable as follows:

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

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

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

If you want to list triggers on a specific table, use the AND clause to concatenate the table name, as follows:

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

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

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

Dropping Triggers (TRIGGERS)

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

sqlite> DROP TRIGGER trigger_name;
Other Extensions