SQLite | Triggers | Creating Triggers

Tags:

A trigger automatically runs SQL when data in a specified table changes.

Create a trigger

CREATE TRIGGER trigger_name [BEFORE | AFTER | INSTEAD OF]
 {DELETE | UPDATE [OF column_name, ...] | INSERT} ON table_name
 [FOR EACH ROW] [WHEN condition]
BEGIN
  statement1;
  statement2;
END;

Triggers can respond to UPDATE, an update of a particular column, DELETE, or INSERT. Values before and after the change can also be referenced.

Create product and log tables, then register one trigger for each operation:

create table product(id integer, name text, price integer);
create table log(id integer primary key, act text);
create trigger itrigger insert on product begin
  insert into log(act) values('INSERT Action');
end;
create trigger dtrigger delete on product begin
  insert into log(act) values('DELETE Action');
end;
create trigger utrigger update on product begin
  insert into log(act) values('UPDATE Action');
end;

insert into product values (1, 'Book', 15000);
update product set price = 25000 where id = 1;
delete from product where id = 1;
select * from log;

1|INSERT Action
2|UPDATE Action
3|DELETE Action

BEFORE and AFTER triggers

BEFORE executes the trigger body before the change; AFTER executes it after the change.

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
BEGIN
  statement1;
END;

SQLite recommends preferring AFTER when possible. If a BEFORE UPDATE or BEFORE DELETE trigger changes the target row, subsequent behavior can be undefined. NEW.rowid is also undefined in a BEFORE INSERT trigger unless explicitly assigned an integer.

FOR EACH ROW

CREATE TRIGGER trigger_name INSERT ON table_name FOR EACH ROW
BEGIN
  statement1;
END;

SQLite currently supports row-level triggers only. FOR EACH ROW is therefore implied when omitted; statement-level triggers are not supported.

SQLite CREATE TRIGGER documentation