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 Monetdb-sql-checkins@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins