# 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.2 { catchsql { CREATE TRIGGER trig UPDATE ON no_such_table BEGIN SELECT * from sqlite_master; END; } } {1 {no such table: no_such_table}} do_test trigger1-1.1.2 { catchsql { CREATE TEMP 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}} # 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}} # Check for memory leaks in the trigger parser # do_test trigger1-2.1 { catchsql { CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT * FROM; -- Syntax error END; } } {1 {near ";": syntax error}} do_test trigger1-2.2 { catchsql { CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT * FROM t1; SELECT * FROM; -- Syntax error END; } } {1 {near ";": syntax error}} # Create a trigger that refers to a table that might not exist. # do_test trigger1-3.1 { execsql { CREATE TEMP TABLE t2(x,y); } catchsql { CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(NEW.a,NEW.b); END; } } {0 {}} do_test trigger-3.2 { catchsql { INSERT INTO t1 VALUES(1,2); SELECT * FROM t2; } } {1 {no such table: main.t2}} do_test trigger-3.3 { db close set rc [catch {sqlite db test.db} err] if {$rc} {lappend rc $err} set rc } {0} do_test trigger-3.4 { catchsql { INSERT INTO t1 VALUES(1,2); SELECT * FROM t2; } } {1 {no such table: main.t2}} do_test trigger-3.5 { catchsql { CREATE TEMP TABLE t2(x,y); INSERT INTO t1 VALUES(1,2); SELECT * FROM t2; } } {1 {no such table: main.t2}} do_test trigger-3.6 { catchsql { DROP TRIGGER r1; CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(NEW.a,NEW.b); END; INSERT INTO t1 VALUES(1,2); SELECT * FROM t2; } } {0 {1 2}} do_test trigger-3.7 { execsql { DROP TABLE t2; CREATE TABLE t2(x,y); SELECT * FROM t2; } } {} do_test trigger-3.8 { execsql { INSERT INTO t1 VALUES(3,4); SELECT * FROM t1 UNION ALL SELECT * FROM t2; } } {1 2 3 4 3 4} do_test trigger-3.9 { db close sqlite db test.db execsql { INSERT INTO t1 VALUES(5,6); SELECT * FROM t1 UNION ALL SELECT * FROM t2; } } {1 2 3 4 5 6 3 4} do_test trigger-4.1 { execsql { CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(NEW.a,NEW.b); END; INSERT INTO t1 VALUES(7,8); SELECT * FROM t2; } } {3 4 7 8} do_test trigger-4.2 { sqlite db2 test.db execsql { INSERT INTO t1 VALUES(9,10); } db2; db2 close execsql { SELECT * FROM t2; } } {3 4 7 8} do_test trigger-4.3 { execsql { DROP TABLE t1; SELECT * FROM t2; }; } {3 4 7 8} do_test trigger-4.4 { db close sqlite db test.db execsql { SELECT * FROM t2; }; } {3 4 7 8} integrity_check trigger-5.1 # Create a trigger with the same name as a table. Make sure the # trigger works. Then drop the trigger. Make sure the table is # still there. # do_test trigger-6.1 { execsql {SELECT type, name FROM sqlite_master} } {view v1 table t2} do_test trigger-6.2 { execsql { CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN SELECT RAISE(ABORT,'deletes are not allows'); END; SELECT type, name FROM sqlite_master; } } {view v1 table t2 trigger t2} do_test trigger-6.3 { catchsql {DELETE FROM t2} } {1 {deletes are not allows}} do_test trigger-6.4 { execsql {SELECT * FROM t2} } {3 4 7 8} do_test trigger-6.5 { db close sqlite db test.db execsql {SELECT type, name FROM sqlite_master} } {view v1 table t2 trigger t2} do_test trigger-6.6 { execsql { DROP TRIGGER t2; SELECT type, name FROM sqlite_master; } } {view v1 table t2} do_test trigger-6.7 { execsql {SELECT * FROM t2} } {3 4 7 8} do_test trigger-6.8 { db close sqlite db test.db execsql {SELECT * FROM t2} } {3 4 7 8} integrity_check trigger-7.1 finish_test