❮ Sqlite Data Types Sqlite Create Database ❯

SQLite Triggers

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

SQLite triggers can be specified to fire when a DELETE, INSERT, or UPDATE occurs on a specific database table, or when an update occurs on 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 trigger actions can access elements of the inserted, deleted, or updated row using the forms NEW.column-name and OLD.column-name, where column-name is the name of the column from the table associated with the trigger.
If a WHEN clause is provided, the SQL statement is executed only for the rows where the WHEN clause is true. If no WHEN clause is provided, the SQL statement is executed for all rows.
The BEFORE or AFTER keywords determine when the trigger action is executed, either before or after the associated row is inserted, modified, or deleted.
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 specified using tablename instead of database.tablename.
A special SQL function RAISE() can be used within the trigger program to throw exceptions.

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, eventname can be *INSERT, DELETE, and UPDATE* database operations on the mentioned table tablename. 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 the UPDATE operation:

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

Example

Let's assume a scenario where we want to maintain an audit trail for every record inserted into a newly created COMPANY table (if it already exists, it will be dropped 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 trail, we will create a new table named AUDIT. Whenever a new entry is inserted into 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 in COMPANY was created. 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 inserting records into the COMPANY table, which will result in creating an audit log record in the AUDIT table. So, let's 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

A record will also be created in the AUDIT table. This record is the result of a trigger, which 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

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

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

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

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

If you want to list triggers on a specific table, use the AND clause with the 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

Dropping Triggers

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

sqlite> DROP TRIGGER trigger_name;

❮ Sqlite Data Types Sqlite Create Database ❯