[DERBY-1613] A trigger does not get invalidated when the view used by it is dropped

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 10.2.1.6
• Component/s: SQL
• Assignee: Yip Ng
• Reporter: Mamta A. Satoor
• Created: 31/Jul/06 06:15
• Updated: 13/Dec/07 09:05
• Resolved: 25/Aug/06 20:33

Description

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;

Issue Links

• is blocked by: DERBY-1621 Trigger action statement is not recompile when there is a change that would affect it.
• is related to: DERBY-1435 Conglomerate does not exist occurs in a specific case after dropping a table referenced by a trigger