Romulo Goncalves wrote: > 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 The new tests for the Trigger tests... Sorry for the wrong log message > > > > > 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
------------------------------------------------------------------------- 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-developers mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-developers
