# The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file tests creating and dropping triggers, and interaction thereof # with the database COMMIT/ROLLBACK logic. # # 1. CREATE and DROP TRIGGER tests # trig-1.1: Error if table does not exist # trig-1.2: Error if trigger already exists # trig-1.3: Created triggers are deleted if the transaction is rolled back # trig-1.4: DROP TRIGGER removes trigger # trig-1.5: Dropped triggers are restored if the transaction is rolled back # trig-1.6: Error if dropped trigger doesn't exist # trig-1.7: Dropping the table automatically drops all triggers # trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master # trig-1.9: Ensure that we cannot create a trigger on sqlite_master # trig-1.10: # trig-1.11: # trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables # trig-1.13: Ensure that AFTER triggers cannot be created on views # trig-1.14: Ensure that BEFORE triggers cannot be created on views # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test trigger1-1.1 { catchsql { CREATE TRIGGER trig UPDATE ON no_such_table BEGIN SELECT * from sqlite_master; END; } } {1 {no such table: no_such_table}} execsql { CREATE TABLE t1(a); } execsql { CREATE TRIGGER tr1 INSERT ON t1 BEGIN INSERT INTO t1 values(1); END; } do_test trigger1-1.2 { catchsql { CREATE TRIGGER tr1 DELETE ON t1 BEGIN SELECT * FROM sqlite_master; END } } {1 {trigger tr1 already exists}} do_test trigger1-1.3 { catchsql { BEGIN; CREATE TRIGGER tr2 INSERT ON t1 BEGIN SELECT * from sqlite_master; END; ROLLBACK; CREATE TRIGGER tr2 INSERT ON t1 BEGIN SELECT * from sqlite_master; END; } } {0 {}} do_test trigger1-1.4 { catchsql { DROP TRIGGER tr1; CREATE TRIGGER tr1 DELETE ON t1 BEGIN SELECT * FROM sqlite_master; END } } {0 {}} do_test trigger1-1.5 { execsql { BEGIN; DROP TRIGGER tr2; ROLLBACK; DROP TRIGGER tr2; } } {} do_test trigger1-1.6 { catchsql { DROP TRIGGER biggles; } } {1 {no such trigger: biggles}} do_test trigger1-1.7 { catchsql { DROP TABLE t1; DROP TRIGGER tr1; } } {1 {no such trigger: tr1}} execsql { CREATE TEMP TABLE temp_table(a); } do_test trigger1-1.8 { execsql { CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN SELECT * from sqlite_master; END; SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig'; } } {0} do_test trigger1-1.9 { catchsql { CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN SELECT * FROM sqlite_master; END; } } {1 {cannot create trigger on system table: sqlite_master}} # Check to make sure that a DELETE statement within the body of # a trigger does not mess up the DELETE that caused the trigger to # run in the first place. # do_test trigger1-1.10 { execsql { create table t1(a,b); insert into t1 values(1,'a'); insert into t1 values(2,'b'); insert into t1 values(3,'c'); insert into t1 values(4,'d'); create trigger r1 after delete on t1 for each row begin delete from t1 WHERE a=old.a+2; end; delete from t1 where a in (1,3); select * from t1; drop table t1; } } {2 b 4 d} do_test trigger1-1.11 { execsql { create table t1(a,b); insert into t1 values(1,'a'); insert into t1 values(2,'b'); insert into t1 values(3,'c'); insert into t1 values(4,'d'); create trigger r1 after update on t1 for each row begin delete from t1 WHERE a=old.a+2; end; update t1 set b='x-' || b where a in (1,3); select * from t1; drop table t1; } } {1 x-a 2 b 4 d} # Ensure that we cannot create INSTEAD OF triggers on tables do_test trigger1-1.12 { catchsql { create table t1(a,b); create trigger t1t instead of update on t1 for each row begin delete from t1 WHERE a=old.a+2; end; } } {1 {cannot create INSTEAD OF trigger on table: t1}} # Ensure that we cannot create BEFORE triggers on views do_test trigger1-1.13 { catchsql { create view v1 as select * from t1; create trigger v1t before update on v1 for each row begin delete from t1 WHERE a=old.a+2; end; } } {1 {cannot create BEFORE trigger on view: v1}} # Ensure that we cannot create AFTER triggers on views do_test trigger1-1.14 { catchsql { create table t1(a,b); create view v1 as select * from t1; create trigger v1t AFTER update on v1 for each row begin delete from t1 WHERE a=old.a+2; end; } } {1 {cannot create AFTER trigger on view: v1}} finish_test