CREATE TRIGGER command is used to create and add triggers to the
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 TRIGGERfollowed by the name of the trigger trigger_name.
AFTERspecifies whether the trigger will be executed before or after the SQL statement is executed.
INSTEAD OFtriggers work only on views.
DELETEspecifies the type of SQL statement that will execute the trigger.
ONtable_name is the name of the table on which the trigger will be executed.
FOR EACH ROWspecifies that the trigger will be executed for each row that is inserted, updated, or deleted.
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.
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;