1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 37c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 47c478bd9Sstevel@tonic-gate# 57c478bd9Sstevel@tonic-gate# May you do good and not evil. 67c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 77c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 87c478bd9Sstevel@tonic-gate# 97c478bd9Sstevel@tonic-gate#*********************************************************************** 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate# This file tests the RAISE() function. 127c478bd9Sstevel@tonic-gate# 137c478bd9Sstevel@tonic-gate 147c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 157c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gate# Test that we can cause ROLLBACK, FAIL and ABORT correctly 187c478bd9Sstevel@tonic-gate# catchsql { DROP TABLE tbl; } 197c478bd9Sstevel@tonic-gatecatchsql { CREATE TABLE tbl (a, b, c) } 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gateexecsql { 22*1da57d55SToomas Soome CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE 237c478bd9Sstevel@tonic-gate WHEN (new.a = 4) THEN RAISE(IGNORE) END; 247c478bd9Sstevel@tonic-gate END; 257c478bd9Sstevel@tonic-gate 26*1da57d55SToomas Soome CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 27*1da57d55SToomas Soome WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') 28*1da57d55SToomas Soome WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') 297c478bd9Sstevel@tonic-gate WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; 307c478bd9Sstevel@tonic-gate END; 317c478bd9Sstevel@tonic-gate} 327c478bd9Sstevel@tonic-gate# ABORT 337c478bd9Sstevel@tonic-gatedo_test trigger3-1.1 { 347c478bd9Sstevel@tonic-gate catchsql { 357c478bd9Sstevel@tonic-gate BEGIN; 367c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (5, 5, 6); 377c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (1, 5, 6); 387c478bd9Sstevel@tonic-gate } 397c478bd9Sstevel@tonic-gate} {1 {Trigger abort}} 407c478bd9Sstevel@tonic-gatedo_test trigger3-1.2 { 417c478bd9Sstevel@tonic-gate execsql { 427c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 437c478bd9Sstevel@tonic-gate ROLLBACK; 447c478bd9Sstevel@tonic-gate } 457c478bd9Sstevel@tonic-gate} {5 5 6} 467c478bd9Sstevel@tonic-gatedo_test trigger3-1.3 { 477c478bd9Sstevel@tonic-gate execsql {SELECT * FROM tbl} 487c478bd9Sstevel@tonic-gate} {} 497c478bd9Sstevel@tonic-gate 507c478bd9Sstevel@tonic-gate# FAIL 517c478bd9Sstevel@tonic-gatedo_test trigger3-2.1 { 527c478bd9Sstevel@tonic-gate catchsql { 537c478bd9Sstevel@tonic-gate BEGIN; 547c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (5, 5, 6); 557c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (2, 5, 6); 567c478bd9Sstevel@tonic-gate } 577c478bd9Sstevel@tonic-gate} {1 {Trigger fail}} 587c478bd9Sstevel@tonic-gatedo_test trigger3-2.2 { 597c478bd9Sstevel@tonic-gate execsql { 607c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 617c478bd9Sstevel@tonic-gate ROLLBACK; 627c478bd9Sstevel@tonic-gate } 637c478bd9Sstevel@tonic-gate} {5 5 6 2 5 6} 647c478bd9Sstevel@tonic-gate# ROLLBACK 657c478bd9Sstevel@tonic-gatedo_test trigger3-3.1 { 667c478bd9Sstevel@tonic-gate catchsql { 677c478bd9Sstevel@tonic-gate BEGIN; 687c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (5, 5, 6); 697c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (3, 5, 6); 707c478bd9Sstevel@tonic-gate } 717c478bd9Sstevel@tonic-gate} {1 {Trigger rollback}} 727c478bd9Sstevel@tonic-gatedo_test trigger3-3.2 { 737c478bd9Sstevel@tonic-gate execsql { 747c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 757c478bd9Sstevel@tonic-gate } 767c478bd9Sstevel@tonic-gate} {} 777c478bd9Sstevel@tonic-gate# IGNORE 787c478bd9Sstevel@tonic-gatedo_test trigger3-4.1 { 797c478bd9Sstevel@tonic-gate catchsql { 807c478bd9Sstevel@tonic-gate BEGIN; 817c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (5, 5, 6); 827c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (4, 5, 6); 837c478bd9Sstevel@tonic-gate } 847c478bd9Sstevel@tonic-gate} {0 {}} 857c478bd9Sstevel@tonic-gatedo_test trigger3-4.2 { 867c478bd9Sstevel@tonic-gate execsql { 877c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 887c478bd9Sstevel@tonic-gate ROLLBACK; 897c478bd9Sstevel@tonic-gate } 907c478bd9Sstevel@tonic-gate} {5 5 6} 917c478bd9Sstevel@tonic-gate 927c478bd9Sstevel@tonic-gate# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE 937c478bd9Sstevel@tonic-gateexecsql {DROP TABLE tbl;} 947c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE tbl (a, b, c);} 957c478bd9Sstevel@tonic-gateexecsql {INSERT INTO tbl VALUES(1, 2, 3);} 967c478bd9Sstevel@tonic-gateexecsql {INSERT INTO tbl VALUES(4, 5, 6);} 977c478bd9Sstevel@tonic-gateexecsql { 987c478bd9Sstevel@tonic-gate CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN 997c478bd9Sstevel@tonic-gate SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 1007c478bd9Sstevel@tonic-gate END; 1017c478bd9Sstevel@tonic-gate 1027c478bd9Sstevel@tonic-gate CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN 1037c478bd9Sstevel@tonic-gate SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 1047c478bd9Sstevel@tonic-gate END; 1057c478bd9Sstevel@tonic-gate} 1067c478bd9Sstevel@tonic-gatedo_test trigger3-5.1 { 1077c478bd9Sstevel@tonic-gate execsql { 1087c478bd9Sstevel@tonic-gate UPDATE tbl SET c = 10; 1097c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 1107c478bd9Sstevel@tonic-gate } 1117c478bd9Sstevel@tonic-gate} {1 2 3 4 5 10} 1127c478bd9Sstevel@tonic-gatedo_test trigger3-5.2 { 1137c478bd9Sstevel@tonic-gate execsql { 1147c478bd9Sstevel@tonic-gate DELETE FROM tbl; 1157c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 1167c478bd9Sstevel@tonic-gate } 1177c478bd9Sstevel@tonic-gate} {1 2 3} 1187c478bd9Sstevel@tonic-gate 1197c478bd9Sstevel@tonic-gate# Check that RAISE(IGNORE) works correctly for nested triggers: 1207c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE tbl2(a, b, c)} 1217c478bd9Sstevel@tonic-gateexecsql { 1227c478bd9Sstevel@tonic-gate CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN 1237c478bd9Sstevel@tonic-gate UPDATE tbl SET c = 10; 1247c478bd9Sstevel@tonic-gate INSERT INTO tbl2 VALUES (new.a, new.b, new.c); 1257c478bd9Sstevel@tonic-gate END; 1267c478bd9Sstevel@tonic-gate} 1277c478bd9Sstevel@tonic-gatedo_test trigger3-6 { 1287c478bd9Sstevel@tonic-gate execsql { 1297c478bd9Sstevel@tonic-gate INSERT INTO tbl2 VALUES (1, 2, 3); 1307c478bd9Sstevel@tonic-gate SELECT * FROM tbl2; 1317c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 1327c478bd9Sstevel@tonic-gate } 1337c478bd9Sstevel@tonic-gate} {1 2 3 1 2 3 1 2 3} 1347c478bd9Sstevel@tonic-gate 1357c478bd9Sstevel@tonic-gate# Check that things also work for view-triggers 1367c478bd9Sstevel@tonic-gateexecsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} 1377c478bd9Sstevel@tonic-gateexecsql { 1387c478bd9Sstevel@tonic-gate CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN 1397c478bd9Sstevel@tonic-gate SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') 140*1da57d55SToomas Soome WHEN (new.a = 2) THEN RAISE(IGNORE) 1417c478bd9Sstevel@tonic-gate WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; 1427c478bd9Sstevel@tonic-gate END; 1437c478bd9Sstevel@tonic-gate} 1447c478bd9Sstevel@tonic-gate 1457c478bd9Sstevel@tonic-gatedo_test trigger3-7.1 { 1467c478bd9Sstevel@tonic-gate catchsql { 1477c478bd9Sstevel@tonic-gate INSERT INTO tbl_view VALUES(1, 2, 3); 1487c478bd9Sstevel@tonic-gate } 1497c478bd9Sstevel@tonic-gate} {1 {View rollback}} 1507c478bd9Sstevel@tonic-gatedo_test trigger3-7.2 { 1517c478bd9Sstevel@tonic-gate catchsql { 1527c478bd9Sstevel@tonic-gate INSERT INTO tbl_view VALUES(2, 2, 3); 1537c478bd9Sstevel@tonic-gate } 1547c478bd9Sstevel@tonic-gate} {0 {}} 1557c478bd9Sstevel@tonic-gatedo_test trigger3-7.3 { 1567c478bd9Sstevel@tonic-gate catchsql { 1577c478bd9Sstevel@tonic-gate INSERT INTO tbl_view VALUES(3, 2, 3); 1587c478bd9Sstevel@tonic-gate } 1597c478bd9Sstevel@tonic-gate} {1 {View abort}} 1607c478bd9Sstevel@tonic-gate 1617c478bd9Sstevel@tonic-gateintegrity_check trigger3-8.1 1627c478bd9Sstevel@tonic-gate 163*1da57d55SToomas Soomecatchsql { DROP TABLE tbl; } 164*1da57d55SToomas Soomecatchsql { DROP TABLE tbl2; } 1657c478bd9Sstevel@tonic-gatecatchsql { DROP VIEW tbl_view; } 1667c478bd9Sstevel@tonic-gate 1677c478bd9Sstevel@tonic-gatefinish_test 168