SQLite Trigger (Trigger)

SQLite triggers are database callback functions that are automatically executed when specified database events occur/Call these functions. The following are key points about SQLite triggers-

• SQLite triggers (Trigger) can be specified to fire on DELETE, INSERT, or UPDATE events on a specific database table, or on updates to one or more specified table columns.

• SQLite only supports FOR EACH ROW triggers (Trigger) and does not support FOR EACH STATEMENT triggers (Trigger). Therefore, explicitly specifying FOR EACH ROW is optional.

• The WHEN clause and trigger actions may access the form used NEW.column-name and OLD.column-name reference elements of the inserted, deleted, or updated rows, where column-name is the name of the column from the table associated with the trigger.

• If a WHEN clause is provided, SQL statements are executed only for the specified rows where the WHEN clause is true. If no WHEN clause is provided, SQL statements are executed for all rows.

• The BEFORE or AFTER keyword determines when the trigger action is executed, deciding whether to execute the trigger action before or after the associated row is inserted, modified, or deleted.

• Triggers are automatically deleted when the table associated with the trigger is 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 use tablename, instead of database.tablename.

• A special SQL function RAISE() can be used to throw exceptions within trigger programs.

Syntax

The following is the basic syntax for creating a trigger.

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

Here,event_namecan be on the above tablesINSERT, DELETEandUPDATEDatabase operationstable_name. You can 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.

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

Example

Let's consider a scenario where we want to audit each record inserted into the COMPANY table, which we have just created (if it already exists, the COMPANY table will be deleted).

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 status, as long as there is a new entry in the COMPANY table, we will create a new table named AUDIT, in which we will insert log messages.

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 a record is created in the COMPANY table. Now let's 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 the actual work, let's start inserting records into the COMPANY table, which will cause an audit log record to be created in the AUDIT table. 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 a record in the COMPANY table, as shown below-

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 we created on the INSERT operation of 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 triggers fromsqlite_masterAll triggers listed in the table, as shown below:

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

The above SQLite statement will list only one entry, as shown below-

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

If you want to list triggers on a specific table, please use the AND clause and table name, as shown below:

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

The above SQLite statement will list only one entry as follows-

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

Delete Trigger

The following DROP command can be used to delete an existing trigger.

sqlite> DROP TRIGGER trigger_name;