[ http://issues.apache.org/jira/browse/DERBY-1613?page=all ]
Yip Ng resolved DERBY-1613.
---------------------------
Fix Version/s: 10.2.1.0
Resolution: Duplicate
DERBY-1621 resolves this issue.
> A trigger does not get invalidated when the view used by it is dropped
> ----------------------------------------------------------------------
>
> Key: DERBY-1613
> URL: http://issues.apache.org/jira/browse/DERBY-1613
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Reporter: Mamta A. Satoor
> Assigned To: Yip Ng
> Fix For: 10.2.1.0
>
>
> I created a trigger which does a select into another table using the rows
> from a view. I had expected the trigger to get invalidated when the view is
> dropped. But that is not happening. Trigger continues to fire with old plan
> which gets it rows from the view. This happens in all the codelines starting
> with 10.0. Once the database is shutdown and restarted, and the next time the
> trigger gets fired, it throws the correct exception for view not found.
> Following is the ij script to reproduce the problem
> connect 'jdbc:derby:c:/dellater/db100;create=true' user 'mamta1' as mamta1;
> create table t11TriggerTest (c111 int not null primary key, c112 int);
> insert into t11TriggerTest values(1,1);
> insert into t11TriggerTest values(2,2);
> -- create a view based on table t11TriggerTest
> create view v21ViewTest as select * from mamta1.t11TriggerTest;
> -- get ready to create a trigger. Trigger is created on t31TriggerTest and it
> inserts into t32TriggerTest
> create table t31TriggerTest (c311 int);
> create table t32TriggerTest (c321 int);
> create trigger tr31t31TriggerTest after insert on t31TriggerTest for each
> statement mode db2sql
> insert into t32TriggerTest values (select c111 from mamta1.v21ViewTest
> where c112=1);
> -- try an insert which will fire the trigger
> insert into t31TriggerTest values(1);
> select * from t31TriggerTest;
> -- we know the trigger got fired if there is one row in t32TriggerTest
> select * from t32TriggerTest;
> -- drop the view used by the trigger.
> drop view v21ViewTest;
> -- try an insert which would cause insert trigger to fire. The insert trigger
> should have failed because view doesn't
> -- exist anymore.
> insert into t31TriggerTest values(1);
> -- no error from the insert trigger fired by insert statement above
> select * from t31TriggerTest;
> -- insert trigger got fired because there are 2 rows in the following table
> now
> select * from t32TriggerTest;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
