db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r523945 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/triggerGeneral.out tests/lang/TriggerTest.java tests/lang/triggerGeneral.sql
Date Fri, 30 Mar 2007 05:19:40 GMT
Author: djd
Date: Thu Mar 29 22:19:39 2007
New Revision: 523945

URL: http://svn.apache.org/viewvc?view=rev&rev=523945
Log:
DERBY-1102 More testing of constraint violations for TriggerTest and remove test cases in
triggerGeneral.sql that are covered by the TriggerTest JUnit test

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=523945&r1=523944&r2=523945
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
Thu Mar 29 22:19:39 2007
@@ -361,178 +361,7 @@
 0 rows inserted/updated/deleted
 ij> drop trigger t5;
 0 rows inserted/updated/deleted
-ij> --
--- Test firing on empty change sets, 
--- statement triggers fire, row triggers
--- do not.
---
-create trigger t1 after insert on t for each row
-	values app.triggerFires('ROW: empty insert, should NOT fire');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on t for each statement
-	values app.triggerFires('STATEMENT: empty insert, ok');
-0 rows inserted/updated/deleted
-ij> insert into t select * from t;
-TRIGGER: <STATEMENT: empty insert, ok> on statement insert into t select * from t
-BEFORE RESULT SET
-<NULL>
-AFTER RESULT SET
-	 X,Y,C
-	 - - -
-0 rows inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
-ij> create trigger t1 after update on t for each row
-	values app.triggerFires('ROW: empty update, should NOT fire');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after update on t for each statement
-	values app.triggerFires('STATEMENT: empty update, ok');
-0 rows inserted/updated/deleted
-ij> update t set x = x;
-TRIGGER: <STATEMENT: empty update, ok> on statement update t set x = x
-BEFORE RESULT SET
-	 X,Y,C
-	 - - -
-AFTER RESULT SET
-	 X,Y,C
-	 - - -
-0 rows inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
-ij> create trigger t1 after delete on t for each row
-	values app.triggerFires('ROW: empty delete, should NOT fire');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after delete on t for each statement
-	values app.triggerFires('STATEMENT: empty delete, ok');
-0 rows inserted/updated/deleted
-ij> delete from t;
-TRIGGER: <STATEMENT: empty delete, ok> on statement delete from t
-BEFORE RESULT SET
-	 X,Y,C
-	 - - -
-AFTER RESULT SET
-<NULL>
-0 rows inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
 ij> drop table x;
-0 rows inserted/updated/deleted
-ij> --
--- Trigger ordering wrt constraints
---
-create table p (x int not null, constraint pk primary key (x));
-0 rows inserted/updated/deleted
-ij> insert into p values 1,2,3;
-3 rows inserted/updated/deleted
-ij> create table f (x int, 
-		constraint ck check (x > 0),
-		constraint fk foreign key (x) references p);
-0 rows inserted/updated/deleted
-ij> create trigger t1 no cascade before insert on f for each row
-	values app.triggerFiresMin('BEFORE constraints');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on f for each row
-	values app.triggerFiresMin('AFTER constraints');
-0 rows inserted/updated/deleted
-ij> -- INSERT
--- fails, ck violated
-insert into f values 0;
-TRIGGER: <BEFORE constraints>
-ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE
on table 'APP.F'.
-ij> alter table f drop constraint ck;
-0 rows inserted/updated/deleted
-ij> -- fails, fk violated
-insert into f values 0;
-TRIGGER: <BEFORE constraints>
-ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key
(0).  The statement has been rolled back.
-ij> alter table f drop foreign key fk;
-0 rows inserted/updated/deleted
-ij> -- ok
-insert into f values 0;
-TRIGGER: <BEFORE constraints>
-TRIGGER: <AFTER constraints>
-1 row inserted/updated/deleted
-ij> delete from f;
-1 row inserted/updated/deleted
-ij> alter table f add constraint ck check (x > 0);
-0 rows inserted/updated/deleted
-ij> alter table f add constraint fk foreign key (x) references p;
-0 rows inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
-ij> insert into f values (1);
-1 row inserted/updated/deleted
-ij> -- UPDATE
-create trigger t1 no cascade before update on f for each row
-	values app.triggerFiresMin('BEFORE constraints');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after update on f for each row
-	values app.triggerFiresMin('AFTER constraints');
-0 rows inserted/updated/deleted
-ij> -- fails, ck violated
-update f set x = 0;
-TRIGGER: <BEFORE constraints>
-ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE
on table 'APP.F'.
-ij> alter table f drop constraint ck;
-0 rows inserted/updated/deleted
-ij> -- fails, fk violated
-update f set x = 0;
-TRIGGER: <BEFORE constraints>
-ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key
(0).  The statement has been rolled back.
-ij> alter table f drop foreign key fk;
-0 rows inserted/updated/deleted
-ij> -- ok
-update f set x = 0;
-TRIGGER: <BEFORE constraints>
-TRIGGER: <AFTER constraints>
-1 row inserted/updated/deleted
-ij> delete from f;
-1 row inserted/updated/deleted
-ij> alter table f add constraint ck check (x > 0);
-0 rows inserted/updated/deleted
-ij> alter table f add constraint fk foreign key (x) references p;
-0 rows inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
-ij> -- DELETE
-insert into f values 1;
-1 row inserted/updated/deleted
-ij> create trigger t1 no cascade before delete on p for each row
-	values app.triggerFiresMin('BEFORE constraints');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after delete on p for each row
-	values app.triggerFiresMin('AFTER constraints');
-0 rows inserted/updated/deleted
-ij> -- fails, fk violated
-delete from p;
-TRIGGER: <BEFORE constraints>
-TRIGGER: <BEFORE constraints>
-TRIGGER: <BEFORE constraints>
-ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key
(1).  The statement has been rolled back.
-ij> alter table f drop foreign key fk;
-0 rows inserted/updated/deleted
-ij> -- ok
-delete from p;
-TRIGGER: <BEFORE constraints>
-TRIGGER: <BEFORE constraints>
-TRIGGER: <BEFORE constraints>
-TRIGGER: <AFTER constraints>
-TRIGGER: <AFTER constraints>
-TRIGGER: <AFTER constraints>
-3 rows inserted/updated/deleted
-ij> drop table f;
-0 rows inserted/updated/deleted
-ij> drop table p;
 0 rows inserted/updated/deleted
 ij> --
 -- Prove that we are firing the proper triggers based

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?view=diff&rev=523945&r1=523944&r2=523945
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Thu Mar 29 22:19:39 2007
@@ -269,6 +269,8 @@
                 "U INT NOT NULL UNIQUE, C INT CHECK (C < 20))");
         s.execute("INSERT INTO T VALUES(1,5,10)");
         s.execute("INSERT INTO T VALUES(11,19,3)");
+        s.execute("CREATE TABLE TCHILD (I INT, FOREIGN KEY (I) REFERENCES T)");
+        s.execute("INSERT INTO TCHILD VALUES 1");
         commit();
         
         int beforeCount = createRandomTriggers()[1];
@@ -283,6 +285,7 @@
         assertStatementError("23505", s, "UPDATE T SET I=1 WHERE I = 11");
         assertFiringOrder("UPDATE", 1, true);        
         info.clear();
+        rollback();
         
         // constraint violation on unique key
         assertStatementError("23505", s, "INSERT INTO T VALUES (2,5,10)");
@@ -291,6 +294,7 @@
         assertStatementError("23505", s, "UPDATE T SET U=5 WHERE I = 11");
         assertFiringOrder("UPDATE", 1, true);        
         info.clear();
+        rollback();
         
         // check constraint
         assertStatementError("23513", s, "INSERT INTO T VALUES (2,6,22)");
@@ -299,7 +303,12 @@
         assertStatementError("23513", s, "UPDATE T SET C=C+40 WHERE I = 11");
         assertFiringOrder("UPDATE", 1, true);        
         info.clear();
-
+        rollback();
+        
+        // Foreign key constraint
+        assertStatementError("23503", s, "DELETE FROM T WHERE I = 1");
+        assertFiringOrder("DELETE", 1, true);        
+        
         s.close();
         commit();
     }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=523945&r1=523944&r2=523945
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Thu Mar 29 22:19:39 2007
@@ -190,114 +190,7 @@
 drop trigger t4;
 drop trigger t5;
 
---
--- Test firing on empty change sets, 
--- statement triggers fire, row triggers
--- do not.
---
-create trigger t1 after insert on t for each row
-	values app.triggerFires('ROW: empty insert, should NOT fire');
-create trigger t2 after insert on t for each statement
-	values app.triggerFires('STATEMENT: empty insert, ok');
-insert into t select * from t;
-drop trigger t1;
-drop trigger t2;
-
-create trigger t1 after update on t for each row
-	values app.triggerFires('ROW: empty update, should NOT fire');
-create trigger t2 after update on t for each statement
-	values app.triggerFires('STATEMENT: empty update, ok');
-update t set x = x;
-drop trigger t1;
-drop trigger t2;
-
-create trigger t1 after delete on t for each row
-	values app.triggerFires('ROW: empty delete, should NOT fire');
-create trigger t2 after delete on t for each statement
-	values app.triggerFires('STATEMENT: empty delete, ok');
-delete from t;
-drop trigger t1;
-drop trigger t2;
 drop table x;
-
---
--- Trigger ordering wrt constraints
---
-create table p (x int not null, constraint pk primary key (x));
-insert into p values 1,2,3;
-create table f (x int, 
-		constraint ck check (x > 0),
-		constraint fk foreign key (x) references p);
-create trigger t1 no cascade before insert on f for each row
-	values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after insert on f for each row
-	values app.triggerFiresMin('AFTER constraints');
-
--- INSERT
--- fails, ck violated
-insert into f values 0;
-
-alter table f drop constraint ck;
-
--- fails, fk violated
-insert into f values 0;
-
-alter table f drop foreign key fk;
-
--- ok
-insert into f values 0;
-
-delete from f;
-alter table f add constraint ck check (x > 0);
-alter table f add constraint fk foreign key (x) references p;
-drop trigger t1;
-drop trigger t2;
-insert into f values (1);
-
-
--- UPDATE
-create trigger t1 no cascade before update on f for each row
-	values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after update on f for each row
-	values app.triggerFiresMin('AFTER constraints');
-
--- fails, ck violated
-update f set x = 0;
-
-alter table f drop constraint ck;
-
--- fails, fk violated
-update f set x = 0;
-
-alter table f drop foreign key fk;
-
--- ok
-update f set x = 0;
-
-delete from f;
-alter table f add constraint ck check (x > 0);
-alter table f add constraint fk foreign key (x) references p;
-drop trigger t1;
-drop trigger t2;
-
-
--- DELETE
-insert into f values 1;
-create trigger t1 no cascade before delete on p for each row
-	values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after delete on p for each row
-	values app.triggerFiresMin('AFTER constraints');
-
--- fails, fk violated
-delete from p;
-
-alter table f drop foreign key fk;
-
--- ok
-delete from p;
-
-drop table f;
-drop table p;
 
 --
 -- Prove that we are firing the proper triggers based



Mime
View raw message