[Monetdb-developers] [Monetdb-sql-checkins] sql/src/test/Triggers cascaded_triggers.sql, SQL_2-18, 1.2, 1.2.4.1 recursive_triggers.sql, SQL_2-18, 1.2, 1.2.4.1 referencing_clause_new.sql, SQL_2-18, 1.4, 1.4.4.1 referencing_clause_old.sql, SQL_2-18, 1.4, 1.4.4.1

Romulo Goncalves R.A.Goncalves at cwi.nl
Fri Jun 8 15:06:00 CEST 2007


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





More information about the developers-list mailing list