[jira] Assigned: (DERBY-1613) A trigger does not get invalidated when the view used by it is dropped

["Yip Ng (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-1613?page=all ]

Yip Ng reassigned DERBY-1613:
-----------------------------

    Assignee: Yip Ng

> 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