SQLite Create Trigger

The SQLite CREATE TRIGGER command is used to create and add triggers to the SQLite database.
Triggers are database operations that run automatically when a specified database event occurs.
The trigger will be executed when an INSERT, UPDATE, or DELETE operation is performed on a table.

The syntax to create a SQLite trigger is as follows:

CREATE TRIGGER syntax

CREATE [ TEMP | TEMPORARY ] TRIGGER [ IF NOT EXISTS ] 
[ SCHEMA_NAME ].trigger_name 
[ BEFORE | AFTER | INSTEAD OF ] 
[ INSERT | UPDATE | DELETE ] 
[ FOR EACH ROW ] 
ON table_name [WHEN condition]
BEGIN
 SQL statements;
END;

• The syntax to create a trigger starts with the command CREATE TRIGGER followed by the name of the trigger trigger_name.
• BEFORE and AFTER specifies whether the trigger will be executed before or after the SQL statement is executed.
• INSTEAD OF triggers work only on views.
• INSERT, UPDATE, DELETE specifies the type of SQL statement that will execute the trigger.
• ON table_name is the name of the table on which the trigger will be executed.
• FOR EACH ROW specifies that the trigger will be executed for each row that is inserted, updated, or deleted.

Examples

CREATE TRIGGER IF NOT EXISTS customers_log_ins
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
INSERT INTO customers_log(id,name,city,has_orders,log_date,log_message) 
VALUES (NEW.id,NEW.name,NEW.city,NEW.has_orders,DATETIME(),'insert');
END;

INSERT INTO customers (id, name, city, has_orders) 
VALUES (9, 'Amelia', 'Denver', NULL);

SELECT * FROM customers_log;

Trigger actions can access column values of the row being inserted, deleted, or updated using references of the form “NEW.column_name” and “OLD.column_name”. Column_name is the name of a column in the table to which the trigger is associated. OLD and NEW references can only be used in event triggers as follows:

• NEW is used in INSERT and UPDATE.
• OLD is used in UPDATE AND DELETE.

CREATE TRIGGER customers_log_upd 
AFTER UPDATE ON customers
WHEN OLD.name <> NEW.name
OR OLD.city <> NEW.city
BEGIN
INSERT INTO customers_log(id,name,city,has_orders,log_date,log_message) 
VALUES (OLD.id,NEW.name,NEW.city,OLD.has_orders,DATETIME(),'update');
END;

UPDATE customers SET name='Mia', city='Boston' WHERE id=9;

SELECT * FROM customers_log;