[ http://issues.apache.org/jira/browse/DERBY-1613?page=comments#action_12430065 ] Yip Ng commented on DERBY-1613: -------------------------------
This is a duplicate to DERBY-1621. > 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 > > 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
