SQLite | Triggers | Referencing Values Before and After a Change

Tags:

Trigger statements can reference inserted values and values before or after an update.

NEW.column and OLD.column

Use the following qualifiers inside SQL executed by a trigger:

NEW.column_name
OLD.column_name

NEW is available for INSERT and UPDATE; OLD is available for DELETE and UPDATE. The column must belong to the table on which the triggering operation runs.

This trigger writes the inserted user’s name to log:

create trigger mytrigger insert on user
begin
insert into log values (new.name);
end;

DELETE trigger example

Create customer and purchase-history tables and add sample data:

create table user (id integer, name text);
create table history (userid integer, goods text, sales integer);
insert into user values (1, 'devkuma');
insert into user values (2, 'kimkc');
insert into user values (3, 'araikuma');
insert into history values (1, 'PC', 550000);
insert into history values (2, 'Mouse', 34000);
insert into history values (1, 'Watch', 85000);
insert into history values (3, 'Light', 24000);
insert into history values (2, 'Mobile', 720000);

When a user is deleted, remove that customer’s history rows by referring to the deleted row’s OLD.id:

create trigger deleteuser delete on user
begin
delete from history where userid = old.id;
end;

delete from user where id = 2;

The user kimkc and the two history rows with userid 2 are deleted.

UPDATE trigger example

Create product and purchase-history tables:

create table product (id integer, name text);
create table history (user text, name text, sales integer);
insert into product values (1, 'Android');
insert into product values (2, 'iPhone');
insert into product values (3, 'iPad');
insert into history values ('devkuma', 'iPhone', 1390000);
insert into history values ('kimkc', 'iPad', 999000);
insert into history values ('araikuma', 'Android', 1092000);
insert into history values ('happykuma', 'iPhone', 965800);

When a product name changes, update matching history rows. OLD.name is the previous value and NEW.name is the replacement:

create trigger updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end;

update product set name = 'iPhone 11' where name = 'iPhone';

Both purchase-history rows formerly named iPhone now contain iPhone 11. The same NEW references can be used by INSERT triggers.