You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2007/03/30 07:19:40 UTC
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
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