/trunk/test/e_droptrigger.test |
@@ -0,0 +1,218 @@ |
# 2010 November 29 |
# |
# 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 implements tests to verify that the "testable statements" in |
# the lang_droptrigger.html document are correct. |
# |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
set ::testprefix e_droptrigger |
|
ifcapable !trigger { finish_test ; return } |
|
proc do_droptrigger_tests {nm args} { |
uplevel do_select_tests [list e_createtable-$nm] $args |
} |
|
proc list_all_triggers {{db db}} { |
set res [list] |
$db eval { PRAGMA database_list } { |
if {$name == "temp"} { |
set tbl sqlite_temp_master |
} else { |
set tbl "$name.sqlite_master" |
} |
lappend res {*}[ |
db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'" |
] |
} |
set res |
} |
|
|
proc droptrigger_reopen_db {{event INSERT}} { |
db close |
forcedelete test.db test.db2 |
sqlite3 db test.db |
|
set ::triggers_fired [list] |
proc r {x} { lappend ::triggers_fired $x } |
db func r r |
|
db eval " |
ATTACH 'test.db2' AS aux; |
|
CREATE TEMP TABLE t1(a, b); |
INSERT INTO t1 VALUES('a', 'b'); |
CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END; |
|
CREATE TABLE t2(a, b); |
INSERT INTO t2 VALUES('a', 'b'); |
CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END; |
CREATE TRIGGER tr2 AFTER $event ON t2 BEGIN SELECT r('main.tr2') ; END; |
|
CREATE TABLE aux.t3(a, b); |
INSERT INTO t3 VALUES('a', 'b'); |
CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END; |
CREATE TRIGGER aux.tr2 AFTER $event ON t3 BEGIN SELECT r('aux.tr2') ; END; |
CREATE TRIGGER aux.tr3 AFTER $event ON t3 BEGIN SELECT r('aux.tr3') ; END; |
" |
} |
|
|
# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt |
# |
do_droptrigger_tests 1.1 -repair { |
droptrigger_reopen_db |
} -tclquery { |
list_all_triggers |
} { |
1 "DROP TRIGGER main.tr1" |
{main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
2 "DROP TRIGGER IF EXISTS main.tr1" |
{main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
3 "DROP TRIGGER tr1" |
{main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
4 "DROP TRIGGER IF EXISTS tr1" |
{main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
|
5 "DROP TRIGGER aux.tr1" |
{main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} |
6 "DROP TRIGGER IF EXISTS aux.tr1" |
{main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} |
|
7 "DROP TRIGGER IF EXISTS aux.xxx" |
{main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
8 "DROP TRIGGER IF EXISTS aux.xxx" |
{main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
} |
|
# EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a |
# trigger created by the CREATE TRIGGER statement. |
# |
foreach {tn tbl droptrigger before after} { |
1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
} { |
|
do_test 2.$tn.1 { |
droptrigger_reopen_db |
execsql " INSERT INTO $tbl VALUES('1', '2') " |
set ::triggers_fired |
} $before |
|
do_test 2.$tn.2 { |
droptrigger_reopen_db |
execsql $droptrigger |
execsql " INSERT INTO $tbl VALUES('1', '2') " |
set ::triggers_fired |
} $after |
} |
|
# EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no |
# longer present in the sqlite_master (or sqlite_temp_master) table and |
# is not fired by any subsequent INSERT, UPDATE or DELETE statements. |
# |
# Test cases e_droptrigger-1.* test the first part of this statement |
# (that dropped triggers do not appear in the schema table), and tests |
# droptrigger-2.* test that dropped triggers are not fired by INSERT |
# statements. The following tests verify that they are not fired by |
# UPDATE or DELETE statements. |
# |
foreach {tn tbl droptrigger before after} { |
1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
} { |
|
do_test 3.1.$tn.1 { |
droptrigger_reopen_db UPDATE |
execsql "UPDATE $tbl SET a = 'abc'" |
set ::triggers_fired |
} $before |
|
do_test 3.1.$tn.2 { |
droptrigger_reopen_db UPDATE |
execsql $droptrigger |
execsql "UPDATE $tbl SET a = 'abc'" |
set ::triggers_fired |
} $after |
} |
foreach {tn tbl droptrigger before after} { |
1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
|
7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
} { |
|
do_test 3.2.$tn.1 { |
droptrigger_reopen_db DELETE |
execsql "DELETE FROM $tbl" |
set ::triggers_fired |
} $before |
|
do_test 3.2.$tn.2 { |
droptrigger_reopen_db DELETE |
execsql $droptrigger |
execsql "DELETE FROM $tbl" |
set ::triggers_fired |
} $after |
} |
|
# EVIDENCE-OF: R-37808-62273 Note that triggers are automatically |
# dropped when the associated table is dropped. |
# |
do_test 4.1 { |
droptrigger_reopen_db |
list_all_triggers |
} {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
do_test 4.2 { |
droptrigger_reopen_db |
execsql "DROP TABLE t1" |
list_all_triggers |
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
do_test 4.3 { |
droptrigger_reopen_db |
execsql "DROP TABLE t1" |
list_all_triggers |
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
do_test 4.4 { |
droptrigger_reopen_db |
execsql "DROP TABLE t1" |
list_all_triggers |
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
|
finish_test |