# 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;
"
}
# -- 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-04950-25529 Once removed, the trigger definition is no
# longer present in the sqlite_schema (or sqlite_temp_schema) 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