OpenSolaris_b135/lib/libsqlite/test/trigger2.test
#pragma ident "%Z%%M% %I% %E% SMI"
# 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.
#
#***********************************************************************
#
# Regression testing of FOR EACH ROW table triggers
#
# 1. Trigger execution order tests.
# These tests ensure that BEFORE and AFTER triggers are fired at the correct
# times relative to each other and the triggering statement.
#
# trigger2-1.1.*: ON UPDATE trigger execution model.
# trigger2-1.2.*: DELETE trigger execution model.
# trigger2-1.3.*: INSERT trigger execution model.
#
# 2. Trigger program execution tests.
# These tests ensure that trigger programs execute correctly (ie. that a
# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
# statements, and combinations thereof).
#
# 3. Selective trigger execution
# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
# with WHEN clauses) are fired only fired when they are supposed to be.
#
# trigger2-3.1: UPDATE OF triggers
# trigger2-3.2: WHEN clause
#
# 4. Cascaded trigger execution
# Tests that trigger-programs may cause other triggers to fire. Also that a
# trigger-program is never executed recursively.
#
# trigger2-4.1: Trivial cascading trigger
# trigger2-4.2: Trivial recursive trigger handling
#
# 5. Count changes behaviour.
# Verify that rows altered by triggers are not included in the return value
# of the "count changes" interface.
#
# 6. ON CONFLICT clause handling
# trigger2-6.1[a-f]: INSERT statements
# trigger2-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# 1.
set ii 0
foreach tbl_defn {
{CREATE TEMP TABLE tbl (a, b);}
{CREATE TABLE tbl (a, b);}
{CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);}
{CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
{CREATE TABLE tbl (a, b PRIMARY KEY);}
{CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
{CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
} {
incr ii
catchsql { DROP INDEX tbl_idx; }
catchsql {
DROP TABLE rlog;
DROP TABLE clog;
DROP TABLE tbl;
DROP TABLE other_tbl;
}
execsql $tbl_defn
execsql {
INSERT INTO tbl VALUES(1, 2);
INSERT INTO tbl VALUES(3, 4);
CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
END;
CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
END;
CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
WHEN old.a = 1
BEGIN
INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
END;
}
do_test trigger2-1.$ii.1 {
execsql {
UPDATE tbl SET a = a * 10, b = b * 10;
SELECT * FROM rlog ORDER BY idx;
SELECT * FROM clog ORDER BY idx;
}
} [list 1 1 2 4 6 10 20 \
2 1 2 13 24 10 20 \
3 3 4 13 24 30 40 \
4 3 4 40 60 30 40 \
1 1 2 13 24 10 20 ]
execsql {
DELETE FROM rlog;
DELETE FROM tbl;
INSERT INTO tbl VALUES (100, 100);
INSERT INTO tbl VALUES (300, 200);
CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
0, 0);
END;
CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
0, 0);
END;
}
do_test trigger2-1.$ii.2 {
execsql {
DELETE FROM tbl;
SELECT * FROM rlog;
}
} [list 1 100 100 400 300 0 0 \
2 100 100 300 200 0 0 \
3 300 200 300 200 0 0 \
4 300 200 0 0 0 0 ]
execsql {
DELETE FROM rlog;
CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
0, 0,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
END;
CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
0, 0,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
END;
}
do_test trigger2-1.$ii.3 {
execsql {
CREATE TABLE other_tbl(a, b);
INSERT INTO other_tbl VALUES(1, 2);
INSERT INTO other_tbl VALUES(3, 4);
-- INSERT INTO tbl SELECT * FROM other_tbl;
INSERT INTO tbl VALUES(5, 6);
DROP TABLE other_tbl;
SELECT * FROM rlog;
}
} [list 1 0 0 0 0 5 6 \
2 0 0 5 6 5 6 ]
do_test trigger2-1.$ii.4 {
execsql {
PRAGMA integrity_check;
}
} {ok}
}
catchsql {
DROP TABLE rlog;
DROP TABLE clog;
DROP TABLE tbl;
DROP TABLE other_tbl;
}
# 2.
set ii 0
foreach tr_program {
{UPDATE tbl SET b = old.b;}
{INSERT INTO log VALUES(new.c, 2, 3);}
{DELETE FROM log WHERE a = 1;}
{INSERT INTO tbl VALUES(500, new.b * 10, 700);
UPDATE tbl SET c = old.c;
DELETE FROM log;}
{INSERT INTO log select * from tbl;}
} {
foreach test_varset [ list \
{
set statement {UPDATE tbl SET c = 10 WHERE a = 1;}
set prep {INSERT INTO tbl VALUES(1, 2, 3);}
set newC 10
set newB 2
set newA 1
set oldA 1
set oldB 2
set oldC 3
} \
{
set statement {DELETE FROM tbl WHERE a = 1;}
set prep {INSERT INTO tbl VALUES(1, 2, 3);}
set oldA 1
set oldB 2
set oldC 3
} \
{
set statement {INSERT INTO tbl VALUES(1, 2, 3);}
set newA 1
set newB 2
set newC 3
}
] \
{
set statement {}
set prep {}
set newA {''}
set newB {''}
set newC {''}
set oldA {''}
set oldB {''}
set oldC {''}
incr ii
eval $test_varset
set statement_type [string range $statement 0 5]
set tr_program_fixed $tr_program
if {$statement_type == "DELETE"} {
regsub -all new\.a $tr_program_fixed {''} tr_program_fixed
regsub -all new\.b $tr_program_fixed {''} tr_program_fixed
regsub -all new\.c $tr_program_fixed {''} tr_program_fixed
}
if {$statement_type == "INSERT"} {
regsub -all old\.a $tr_program_fixed {''} tr_program_fixed
regsub -all old\.b $tr_program_fixed {''} tr_program_fixed
regsub -all old\.c $tr_program_fixed {''} tr_program_fixed
}
set tr_program_cooked $tr_program
regsub -all new\.a $tr_program_cooked $newA tr_program_cooked
regsub -all new\.b $tr_program_cooked $newB tr_program_cooked
regsub -all new\.c $tr_program_cooked $newC tr_program_cooked
regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked
regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked
regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked
catchsql {
DROP TABLE tbl;
DROP TABLE log;
}
execsql {
CREATE TABLE tbl(a PRIMARY KEY, b, c);
CREATE TABLE log(a, b, c);
}
set query {SELECT * FROM tbl; SELECT * FROM log;}
set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\
INSERT INTO log VALUES(10, 20, 30);"
# Check execution of BEFORE programs:
set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
execsql "DELETE FROM tbl; DELETE FROM log; $prep";
execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\
ON tbl BEGIN $tr_program_fixed END;"
do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data
execsql "DROP TRIGGER the_trigger;"
execsql "DELETE FROM tbl; DELETE FROM log;"
# Check execution of AFTER programs
set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
execsql "DELETE FROM tbl; DELETE FROM log; $prep";
execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
ON tbl BEGIN $tr_program_fixed END;"
do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
execsql "DROP TRIGGER the_trigger;"
do_test trigger2-2.$ii-integrity {
execsql {
PRAGMA integrity_check;
}
} {ok}
}
}
catchsql {
DROP TABLE tbl;
DROP TABLE log;
}
# 3.
# trigger2-3.1: UPDATE OF triggers
execsql {
CREATE TABLE tbl (a, b, c, d);
CREATE TABLE log (a);
INSERT INTO log VALUES (0);
INSERT INTO tbl VALUES (0, 0, 0, 0);
INSERT INTO tbl VALUES (1, 0, 0, 0);
CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
BEGIN
UPDATE log SET a = a + 1;
END;
}
do_test trigger2-3.1 {
execsql {
UPDATE tbl SET b = 1, c = 10; -- 2
UPDATE tbl SET b = 10; -- 0
UPDATE tbl SET d = 4 WHERE a = 0; --1
UPDATE tbl SET a = 4, b = 10; --0
SELECT * FROM log;
}
} {3}
execsql {
DROP TABLE tbl;
DROP TABLE log;
}
# trigger2-3.2: WHEN clause
set when_triggers [ list \
{t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
{t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]
execsql {
CREATE TABLE tbl (a, b, c, d);
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;"
}
do_test trigger2-3.2 {
execsql {
INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1
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;
}
} {1 0 1}
execsql {
DROP TABLE tbl;
DROP TABLE log;
}
do_test trigger2-3.3 {
execsql {
PRAGMA integrity_check;
}
} {ok}
# Simple cascaded trigger
execsql {
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 trigger
execsql {
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;
}
# 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;
}
# 7. Triggers on views
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}
do_test trigger2-9.9 {
execsql {PRAGMA integrity_check}
} {ok}
finish_test