CREATE TABLE log (a);  INSERT INTO log VALUES (0);}foreach trig $when_triggers {  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"}ifcapable subquery {  set t232 {1 0 1}} else {  set t232 {0 0 1}}do_test trigger2-3.2 {  execsql {     INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1 (ifcapable subquery)    SELECT * FROM log;    UPDATE log SET a = 0;    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0    SELECT * FROM log;    UPDATE log SET a = 0;    INSERT INTO tbl VALUES(200, 0, 0, 0);     -- 1    SELECT * FROM log;    UPDATE log SET a = 0;  }} $t232execsql {  DROP TABLE tbl;  DROP TABLE log;}integrity_check trigger2-3.3# Simple cascaded triggerexecsql {  CREATE TABLE tblA(a, b);  CREATE TABLE tblB(a, b);  CREATE TABLE tblC(a, b);  CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN    INSERT INTO tblB values(new.a, new.b);  END;  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN    INSERT INTO tblC values(new.a, new.b);  END;}do_test trigger2-4.1 {  execsql {    INSERT INTO tblA values(1, 2);    SELECT * FROM tblA;    SELECT * FROM tblB;    SELECT * FROM tblC;  }} {1 2 1 2 1 2}execsql {  DROP TABLE tblA;  DROP TABLE tblB;  DROP TABLE tblC;}# Simple recursive triggerexecsql {  CREATE TABLE tbl(a, b, c);  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl     BEGIN      INSERT INTO tbl VALUES (new.a, new.b, new.c);    END;}do_test trigger2-4.2 {  execsql {    INSERT INTO tbl VALUES (1, 2, 3);    select * from tbl;  }} {1 2 3 1 2 3}execsql {  DROP TABLE tbl;}# 5.execsql {  CREATE TABLE tbl(a, b, c);  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl     BEGIN      INSERT INTO tbl VALUES (1, 2, 3);      INSERT INTO tbl VALUES (2, 2, 3);      UPDATE tbl set b = 10 WHERE a = 1;      DELETE FROM tbl WHERE a = 1;      DELETE FROM tbl;    END;}do_test trigger2-5 {  execsql {    INSERT INTO tbl VALUES(100, 200, 300);  }  db changes} {1}execsql {  DROP TABLE tbl;}ifcapable conflict {  # Handling of ON CONFLICT by INSERT statements inside triggers  execsql {    CREATE TABLE tbl (a primary key, b, c);    CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN      INSERT OR IGNORE INTO tbl values (new.a, 0, 0);    END;  }  do_test trigger2-6.1a {    execsql {      BEGIN;      INSERT INTO tbl values (1, 2, 3);      SELECT * from tbl;    }  } {1 2 3}  do_test trigger2-6.1b {    catchsql {      INSERT OR ABORT INTO tbl values (2, 2, 3);    }  } {1 {column a is not unique}}  do_test trigger2-6.1c {    execsql {      SELECT * from tbl;    }  } {1 2 3}  do_test trigger2-6.1d {    catchsql {      INSERT OR FAIL INTO tbl values (2, 2, 3);    }  } {1 {column a is not unique}}  do_test trigger2-6.1e {    execsql {      SELECT * from tbl;    }  } {1 2 3 2 2 3}  do_test trigger2-6.1f {    execsql {      INSERT OR REPLACE INTO tbl values (2, 2, 3);      SELECT * from tbl;    }  } {1 2 3 2 0 0}  do_test trigger2-6.1g {    catchsql {      INSERT OR ROLLBACK INTO tbl values (3, 2, 3);    }  } {1 {column a is not unique}}  do_test trigger2-6.1h {    execsql {      SELECT * from tbl;    }  } {}  execsql {DELETE FROM tbl}      # Handling of ON CONFLICT by UPDATE statements inside triggers  execsql {    INSERT INTO tbl values (4, 2, 3);    INSERT INTO tbl values (6, 3, 4);    CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN      UPDATE OR IGNORE tbl SET a = new.a, c = 10;    END;  }  do_test trigger2-6.2a {    execsql {      BEGIN;      UPDATE tbl SET a = 1 WHERE a = 4;      SELECT * from tbl;    }  } {1 2 10 6 3 4}  do_test trigger2-6.2b {    catchsql {      UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;    }  } {1 {column a is not unique}}  do_test trigger2-6.2c {    execsql {      SELECT * from tbl;    }  } {1 2 10 6 3 4}  do_test trigger2-6.2d {    catchsql {      UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;    }  } {1 {column a is not unique}}  do_test trigger2-6.2e {    execsql {      SELECT * from tbl;    }  } {4 2 10 6 3 4}  do_test trigger2-6.2f.1 {    execsql {      UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;      SELECT * from tbl;    }  } {1 3 10}  do_test trigger2-6.2f.2 {    execsql {      INSERT INTO tbl VALUES (2, 3, 4);      SELECT * FROM tbl;    }  } {1 3 10 2 3 4}  do_test trigger2-6.2g {    catchsql {      UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;    }  } {1 {column a is not unique}}  do_test trigger2-6.2h {    execsql {      SELECT * from tbl;    }  } {4 2 3 6 3 4}  execsql {    DROP TABLE tbl;  }} ; # ifcapable conflict# 7. Triggers on viewsifcapable view {do_test trigger2-7.1 {  execsql {  CREATE TABLE ab(a, b);  CREATE TABLE cd(c, d);  INSERT INTO ab VALUES (1, 2);  INSERT INTO ab VALUES (0, 0);  INSERT INTO cd VALUES (3, 4);  CREATE TABLE tlog(ii INTEGER PRIMARY KEY,       olda, oldb, oldc, oldd, newa, newb, newc, newd);  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;  CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);  END;  CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);  END;  CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	old.a, old.b, old.c, old.d, 0, 0, 0, 0);  END;  CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	old.a, old.b, old.c, old.d, 0, 0, 0, 0);  END;  CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	0, 0, 0, 0, new.a, new.b, new.c, new.d);  END;   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN    INSERT INTO tlog VALUES(NULL, 	0, 0, 0, 0, new.a, new.b, new.c, new.d);   END;  }} {};do_test trigger2-7.2 {  execsql {    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;    DELETE FROM abcd WHERE a = 1;    INSERT INTO abcd VALUES(10, 20, 30, 40);    SELECT * FROM tlog;  }} [ list 1 1 2 3 4 100 25 3 4 \         2 1 2 3 4 100 25 3 4 \	 3 1 2 3 4 0 0 0 0 \	 4 1 2 3 4 0 0 0 0 \	 5 0 0 0 0 10 20 30 40 \	 6 0 0 0 0 10 20 30 40 ]do_test trigger2-7.3 {  execsql {    DELETE FROM tlog;    INSERT INTO abcd VALUES(10, 20, 30, 40);    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;    DELETE FROM abcd WHERE a = 1;    SELECT * FROM tlog;  }} [ list \   1 0 0 0 0 10 20 30 40 \   2 0 0 0 0 10 20 30 40 \   3 1 2 3 4 100 25 3 4 \   4 1 2 3 4 100 25 3 4 \   5 1 2 3 4 0 0 0 0 \   6 1 2 3 4 0 0 0 0 \]do_test trigger2-7.4 {  execsql {    DELETE FROM tlog;    DELETE FROM abcd WHERE a = 1;    INSERT INTO abcd VALUES(10, 20, 30, 40);    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;    SELECT * FROM tlog;  }} [ list \   1 1 2 3 4 0 0 0 0 \   2 1 2 3 4 0 0 0 0 \   3 0 0 0 0 10 20 30 40 \   4 0 0 0 0 10 20 30 40 \   5 1 2 3 4 100 25 3 4 \   6 1 2 3 4 100 25 3 4 \]do_test trigger2-8.1 {  execsql {    CREATE TABLE t1(a,b,c);    INSERT INTO t1 VALUES(1,2,3);    CREATE VIEW v1 AS      SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;    SELECT * FROM v1;  }} {3 5 4}do_test trigger2-8.2 {  execsql {    CREATE TABLE v1log(a,b,c,d,e,f);    CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN      INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);    END;    DELETE FROM v1 WHERE x=1;    SELECT * FROM v1log;  }} {}do_test trigger2-8.3 {  execsql {    DELETE FROM v1 WHERE x=3;    SELECT * FROM v1log;  }} {3 {} 5 {} 4 {}}do_test trigger2-8.4 {  execsql {    INSERT INTO t1 VALUES(4,5,6);    DELETE FROM v1log;    DELETE FROM v1 WHERE y=11;    SELECT * FROM v1log;  }} {9 {} 11 {} 10 {}}do_test trigger2-8.5 {  execsql {    CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN      INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);    END;    DELETE FROM v1log;    INSERT INTO v1 VALUES(1,2,3);    SELECT * FROM v1log;  }} {{} 1 {} 2 {} 3}do_test trigger2-8.6 {  execsql {    CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN      INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);    END;    DELETE FROM v1log;    UPDATE v1 SET x=x+100, y=y+200, z=z+300;    SELECT * FROM v1log;  }} {3 103 5 205 4 304 9 109 11 211 10 310}} ;# ifcapable viewintegrity_check trigger2-9.9finish_test