Update of /cvsroot/monetdb/sql/src/test/Triggers
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv21172
Modified Files:
Tag: SQL_2-18
cascaded_triggers.sql recursive_triggers.sql
referencing_clause_new.sql referencing_clause_old.sql
Log Message:
The new tests for UPDATE/DELETE CASCADE
Index: referencing_clause_new.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/test/Triggers/referencing_clause_new.sql,v
retrieving revision 1.4
retrieving revision 1.4.4.1
diff -u -d -r1.4 -r1.4.4.1
--- referencing_clause_new.sql 7 Dec 2006 15:26:06 -0000 1.4
+++ referencing_clause_new.sql 8 Jun 2007 12:47:50 -0000 1.4.4.1
@@ -1,101 +1,199 @@
+--this test only tests the sintax
--the semantic should also be tested after the syntax test
create table t1 (id int, name varchar(1024));
+create table t2 (id int, name varchar(1024));
---test when the trigger event is UPDATE
+--test when trigger event is UPDATE
insert into t1 values(10, 'monetdb');
insert into t1 values(20, 'monet');
create trigger test1
after update on t1 referencing new row as new_row
- for each row insert into t1 values(0, 'update_new_row');
+ for each row insert into t2 values(0, 'update_new_row');
create trigger test2
after update on t1 referencing new row new_row
- for each row insert into t1 values(1, 'update_new_row');
+ for each row insert into t2 values(1, 'update_new_row');
create trigger test3
after update on t1 referencing new as new_row
- for each row insert into t1 values(2, 'update_new_row');
+ for each row insert into t2 values(2, 'update_new_row');
create trigger test4
after update on t1 referencing new new_row
- for each row insert into t1 values(3, 'update_new_row');
+ for each row insert into t2 values(3, 'update_new_row');
update t1 set name = 'mo' where id = 10;
select * from t1;
+select * from t2;
+
+delete from t1 where id > -1;
+delete from t2 where id > -1;
+
+drop trigger test1;
+drop trigger test2;
+drop trigger test3;
+drop trigger test4;
+
+--test when trigger event is DELETE
+insert into t1 values(10, 'monetdb');
+insert into t1 values(20, 'monet');
+
+create trigger test1
+ after delete on t1 referencing new row as new_row
+ for each row insert into t2 values(0, 'delete_new_row');
+
+create trigger test2
+ after delete on t1 referencing new row new_row
+ for each row insert into t2 values(1, 'delete_new_row');
+
+create trigger test3
+ after delete on t1 referencing new as new_row
+ for each row insert into t2 values(2, 'delete_new_row');
+
+create trigger test4
+ after delete on t1 referencing new new_row
+ for each row insert into t2 values(3, 'delete_new_row');
+
delete from t1 where id >-1;
+select * from t1;
+select * from t2;
+
drop trigger test1;
drop trigger test2;
drop trigger test3;
drop trigger test4;
---test when the trigger event is INSERT
+delete from t2 where id >-1;
+
+--test error messages
+--new row and new table are not allowed if the Trigger event is INSERT
+
insert into t1 values(10, 'monetdb');
create trigger test1
after insert on t1 referencing new row as new_row
- for each row insert into t1 values(0, 'insert_new_row');
+ for each row insert into t2 values(0, 'insert_new_row');
create trigger test2
after insert on t1 referencing new row new_row
- for each row insert into t1 values(1, 'insert_new_row');
+ for each row insert into t2 values(1, 'insert_new_row');
create trigger test3
after insert on t1 referencing new as new_row
- for each row insert into t1 values(2, 'insert_new_row');
+ for each row insert into t2 values(2, 'insert_new_row');
create trigger test4
after insert on t1 referencing new new_row
- for each row insert into t1 values(3, 'insert_new_row');
+ for each row insert into t2 values(3, 'insert_new_row');
+
insert into t1 values(20, 'monet');
select * from t1;
+select * from t2;
-delete from t1 where id > -1;
+delete from t1 where id >-1;
+delete from t2 where id >-1;
drop trigger test1;
drop trigger test2;
drop trigger test3;
drop trigger test4;
---test error messages
---new row and new table are not allowed if the Trigger event is DELETE
+--test with new row and new table and mixed
insert into t1 values(10, 'monetdb');
insert into t1 values(20, 'monet');
create trigger test1
- after delete on t1 referencing new row as new_row
- for each row insert into t1 values(0, 'delete_new_row');
+ after update on t1 referencing new row as new_row new table as new_table
+ for each row insert into t2 values(0, 'insert_new_row_table');
create trigger test2
- after delete on t1 referencing new row new_row
- for each row insert into t1 values(1, 'delete_new_row');
+ after update on t1 referencing new row new_row new row as new_row
+ for each row insert into t2 values(1, 'insert_new_new_row');
create trigger test3
- after delete on t1 referencing new as new_row
- for each row insert into t1 values(2, 'delete_new_row');
+ after update on t1 referencing new table as new_table new table as
new_table
+ for each row insert into t2 values(2, 'insert_new__new_table');
create trigger test4
- after delete on t1 referencing new new_row
- for each row insert into t1 values(3, 'delete_new_row');
+ after update on t1 referencing new row as new_row new table as new_table
+ for each row insert into t2 values(3, 'insert_new_row_new_table');
+create trigger test5
+ after update on t1 referencing new table as new_table new row as new_row
+ for each row insert into t2 values(4, 'insert_new_table_new_row');
-delete from t1 where id >1;
+
+update t1 set name = 'mo' where id = 10;
select * from t1;
+select * from t2;
+
+delete from t1 where id >-1;
+delete from t2 where id >-1;
+
drop trigger test1;
drop trigger test2;
drop trigger test3;
drop trigger test4;
+drop trigger test5;
+
+--test stanger combinations
+
+insert into t1 values(10, 'monetdb');
+
+create trigger test1
+ after update on t1 referencing new row as new_row new table as new_table
+ for each row insert into t2 values(0, 'update_new_row_new_table');
+
+create trigger test2
+ after insert on t1 referencing new row new_row new row as new_row
+ for each row insert into t2 values(1, 'insert_new_new_row');
+
+create trigger test3
+ after delete on t1 referencing new row new_row new row as new_row
+ for each row insert into t2 values(2, 'delete_new_new_row');
+
+create trigger test4
+ after delete on t1 referencing new row as new_row new table as new_table
+ for each row insert into t2 values(3, 'delete_new_row_new_table');
+
+create trigger test5
+ after insert on t1 referencing new table as new_table new row as new_row
+ for each row insert into t2 values(4, 'insert_new_table_new_row');
+
+insert into t1 values(20, 'monet');
+select * from t1;
+select * from t2;
+
+update t1 set name = 'mo' where id = 10;
+select * from t1;
+select * from t2;
+
+delete from t1 where id >5;
+select * from t1;
+select * from t2;
+
+
+drop trigger test1;
+drop trigger test2;
+drop trigger test3;
+drop trigger test4;
+drop trigger test5;
+
+delete from t1 where id >-1;
+delete from t2 where id >-1;
--Cleanup
drop table t1;
+drop table t2;
Index: cascaded_triggers.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/test/Triggers/cascaded_triggers.sql,v
retrieving revision 1.2
retrieving revision 1.2.4.1
diff -u -d -r1.2 -r1.2.4.1
--- cascaded_triggers.sql 6 Dec 2006 10:27:38 -0000 1.2
+++ cascaded_triggers.sql 8 Jun 2007 12:47:49 -0000 1.2.4.1
@@ -1,53 +1,31 @@
---test the semantic of cascaded triggers
create table t1(id int, name varchar(1024), age int);
-
create table t2(id int, age int);
create trigger test_0 after insert on t1
- update t1 set id = 1, name = 'monetdb', age = 24 where id =1;
-
-insert into t1 values(1, 'mo', 25);
-
-select * from t1;
-
-select * from t2;
-
-create trigger test_1 after insert on t1
insert into t2 select id,age from t1;
-insert into t1 values(2, 'mo', 26);
-
-select * from t1;
-
-select * from t2;
-
-create trigger test_2 after insert on t1
- insert into t2 values(1,23);
-
-insert into t1 values(3, 'mo', 27);
+insert into t1 values(1, 'mo', 25);
select * from t1;
-
select * from t2;
-create trigger test_3 after delete on t1
- delete from t1 where id =3;
+create trigger test_1 after delete on t1
+ insert into t1 values(3, 'mo', 27);
delete from t1 where id = 1;
select * from t1;
-
select * from t2;
-create trigger test_4 after update on t1
- update t1
- set age = 27
- where id = 2;
+create trigger test_2 after update on t1
+ delete from t2;
+
+create trigger test_3 after delete on t2
+ insert into t1 values(1, 'mo', 25);
update t1 set name = 'monet' where id = 2;
select * from t1;
-
select * from t2;
drop trigger test_0;
@@ -58,8 +36,6 @@
drop trigger test_3;
-drop trigger test_4;
-
drop table t1;
drop table t2;
Index: referencing_clause_old.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/test/Triggers/referencing_clause_old.sql,v
retrieving revision 1.4
retrieving revision 1.4.4.1
diff -u -d -r1.4 -r1.4.4.1
--- referencing_clause_old.sql 8 Dec 2006 12:17:42 -0000 1.4
+++ referencing_clause_old.sql 8 Jun 2007 12:47:50 -0000 1.4.4.1
@@ -2,6 +2,7 @@
--the semantic should also be tested after the syntax test
create table t1 (id int, name varchar(1024));
+create table t2 (id int, name varchar(1024));
--test when trigger event is UPDATE
insert into t1 values(10, 'monetdb');
@@ -10,26 +11,28 @@
create trigger test1
after update on t1 referencing old row as old_row
- for each row insert into t1 values(0, 'update_old_row');
+ for each row insert into t2 values(0, 'update_old_row');
create trigger test2
after update on t1 referencing old row old_row
- for each row insert into t1 values(1, 'update_old_row');
+ for each row insert into t2 values(1, 'update_old_row');
create trigger test3
after update on t1 referencing old as old_row
- for each row insert into t1 values(2, 'update_old_row');
+ for each row insert into t2 values(2, 'update_old_row');
create trigger test4
after update on t1 referencing old old_row
- for each row insert into t1 values(3, 'update_old_row');
+ for each row insert into t2 values(3, 'update_old_row');
update t1 set name = 'mo' where id = 10;
select * from t1;
+select * from t2;
delete from t1 where id > -1;
+delete from t2 where id > -1;
drop trigger test1;
drop trigger test2;
@@ -42,31 +45,32 @@
create trigger test1
after delete on t1 referencing old row as old_row
- for each row insert into t1 values(0, 'delete_old_row');
+ for each row insert into t2 values(0, 'delete_old_row');
create trigger test2
after delete on t1 referencing old row old_row
- for each row insert into t1 values(1, 'delete_old_row');
+ for each row insert into t2 values(1, 'delete_old_row');
create trigger test3
after delete on t1 referencing old as old_row
- for each row insert into t1 values(2, 'delete_old_row');
+ for each row insert into t2 values(2, 'delete_old_row');
create trigger test4
after delete on t1 referencing old old_row
- for each row insert into t1 values(3, 'delete_old_row');
+ for each row insert into t2 values(3, 'delete_old_row');
delete from t1 where id >-1;
select * from t1;
+select * from t2;
drop trigger test1;
drop trigger test2;
drop trigger test3;
drop trigger test4;
-delete from t1 where id >-1;
+delete from t2 where id >-1;
--test error messages
--old row and old table are not allowed if the Trigger event is INSERT
@@ -75,26 +79,28 @@
create trigger test1
after insert on t1 referencing old row as old_row
- for each row insert into t1 values(0, 'insert_old_row');
+ for each row insert into t2 values(0, 'insert_old_row');
create trigger test2
after insert on t1 referencing old row old_row
- for each row insert into t1 values(1, 'insert_old_row');
+ for each row insert into t2 values(1, 'insert_old_row');
create trigger test3
after insert on t1 referencing old as old_row
- for each row insert into t1 values(2, 'insert_old_row');
+ for each row insert into t2 values(2, 'insert_old_row');
create trigger test4
after insert on t1 referencing old old_row
- for each row insert into t1 values(3, 'insert_old_row');
+ for each row insert into t2 values(3, 'insert_old_row');
insert into t1 values(20, 'monet');
select * from t1;
+select * from t2;
delete from t1 where id >-1;
+delete from t2 where id >-1;
drop trigger test1;
drop trigger test2;
@@ -108,30 +114,33 @@
create trigger test1
after update on t1 referencing old row as old_row old table as old_table
- for each row insert into t1 values(0, 'insert_old_row_table');
+ for each row insert into t2 values(0, 'insert_old_row_table');
create trigger test2
after update on t1 referencing old row old_row new row as new_row
- for each row insert into t1 values(1, 'insert_old_new_row');
+ for each row insert into t2 values(1, 'insert_old_new_row');
create trigger test3
after update on t1 referencing old table as old_table new table as
new_table
- for each row insert into t1 values(2, 'insert_old__new_table');
+ for each row insert into t2 values(2, 'insert_old__new_table');
create trigger test4
after update on t1 referencing old row as old_row new table as new_table
- for each row insert into t1 values(3, 'insert_old_row_new_table');
+ for each row insert into t2 values(3, 'insert_old_row_new_table');
create trigger test5
after update on t1 referencing old table as old_table new row as new_row
- for each row insert into t1 values(3, 'insert_old_table_new_row');
+ for each row insert into t2 values(4, 'insert_old_table_new_row');
update t1 set name = 'mo' where id = 10;
select * from t1;
+select * from t2;
delete from t1 where id >-1;
+delete from t2 where id >-1;
+
drop trigger test1;
drop trigger test2;
@@ -145,32 +154,35 @@
create trigger test1
after update on t1 referencing old row as old_row new table as new_table
- for each row insert into t1 values(0, 'update_old_row__new_table');
+ for each row insert into t2 values(0, 'update_old_row_new_table');
create trigger test2
after insert on t1 referencing old row old_row new row as new_row
- for each row insert into t1 values(1, 'insert_old_new_row');
+ for each row insert into t2 values(1, 'insert_old_new_row');
create trigger test3
after delete on t1 referencing old row old_row new row as new_row
- for each row insert into t1 values(1, 'delete_old_new_row');
+ for each row insert into t2 values(2, 'delete_old_new_row');
create trigger test4
after delete on t1 referencing old row as old_row new table as new_table
- for each row insert into t1 values(3, 'delete_old_row_new_table');
+ for each row insert into t2 values(3, 'delete_old_row_new_table');
create trigger test5
after insert on t1 referencing old table as old_table new row as new_row
- for each row insert into t1 values(3, 'insert_old_table_new_row');
+ for each row insert into t2 values(4, 'insert_old_table_new_row');
insert into t1 values(20, 'monet');
select * from t1;
+select * from t2;
update t1 set name = 'mo' where id = 10;
select * from t1;
+select * from t2;
delete from t1 where id >5;
select * from t1;
+select * from t2;
drop trigger test1;
@@ -180,6 +192,8 @@
drop trigger test5;
delete from t1 where id >-1;
+delete from t2 where id >-1;
--Cleanup
drop table t1;
+drop table t2;
Index: recursive_triggers.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/test/Triggers/recursive_triggers.sql,v
retrieving revision 1.2
retrieving revision 1.2.4.1
diff -u -d -r1.2 -r1.2.4.1
--- recursive_triggers.sql 6 Dec 2006 10:27:38 -0000 1.2
+++ recursive_triggers.sql 8 Jun 2007 12:47:49 -0000 1.2.4.1
@@ -1,57 +1,62 @@
create table t1(id int, name varchar(1024), age int);
create table t2(id int, age int);
+--the trigger calls itself
create trigger test_0 after insert on t1
- insert into t1 values(1, 'monetdb', 24);
-
-insert into t1 values(1, 'mo', 25);
+ insert into t1 values(3, 'mo', 27);
-select * from t1;
-select * from t2;
+drop trigger test_0;
-create trigger test_1 after insert on t1
+--recursivity of 2 levels
+create trigger test_0 after insert on t1
insert into t2 select id,age from t1;
+create trigger test_1 after insert on t2
+ insert into t1 values(3, 'mo', 27);
-insert into t1 values(2, 'mo', 26);
-select * from t1;
-select * from t2;
+drop trigger test_0;
+drop trigger test_1;
-create trigger test_2 before insert on t1
- insert into t2 values(1,23);
+--recursivity of n levels
+create trigger test_0 after insert on t1
+ insert into t2 select id,age from t1;
-insert into t1 values(3, 'mo', 27);
+create trigger test_1 after insert on t2
+ delete from t2;
-select * from t1;
-select * from t2;
+create trigger test_2 after delete on t2
+ insert into t1 values(3, 'mo', 27);
-create trigger test_3 after delete on t1
- delete from t1 where id =3;
-delete from t1 where id = 1;
+drop trigger test_0;
+drop trigger test_1;
+drop trigger test_2;
-select * from t1;
-select * from t2;
+--recursivity with procedure calls
+create PROCEDURE p1(id int, age int)
+BEGIN
+ insert into t2 values(id, age);
+END;
-create trigger test_4 after update on t1
- update t1
- set age = 27
- where id = 2;
+create PROCEDURE p1()
+BEGIN
+ declare id int, age int;
+ set id = 1;
+ set age = 23;
+ call p1(id, age);
+END;
-update t1 set name = 'monet' where id = 2;
+create trigger test_0 after insert on t2
+BEGIN ATOMIC
+ insert into t1 values(1, 'monetdb', 24);
+ call p1();
+END;
-select * from t1;
-select * from t2;
+--insert into t2 values(0, 24);
drop trigger test_0;
-drop trigger test_1;
-
-drop trigger test_2;
-
-drop trigger test_3;
-
-drop trigger test_4;
+drop ALL procedure p1;
drop table t1;
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
