|
If you were logged in you would be able to see more operations.
|
|
|
|
Issue Links:
|
Blocker
|
|
|
|
This issue is blocked by:
|
|
DERBY-1621
Trigger action statement is not recompile when there is a change that would affect it.
|
|
|
|
|
Reference
|
|
|
|
This issue is related to:
|
|
|
DERBY-1435
Conglomerate does not exist occurs in a specific case after dropping a table referenced by a trigger
|
|
|
|
|
|
|
| Resolution Date: |
25/Aug/06 08:33 PM
|
|
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;
|
|
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;
|
Show » |
|
Bug
A trigger does not get invalidated when the view used by it is dropped
DERBY-1435. I had filedDERBY-1435for the case where we drop a table referenced by a trigger. And then found similar problem when we drop procedures/functions referenced by trigger. In these cases, trigger firing does not throw any exception if we re-execute a previously executed statement. If database is restarted, we get the correct exception indicating trigger is invalid. I found this is the case with the Mamta's repro for views too. I haven't yet looked to see if these issues can be solved by a single solution. So I am just linking this issue toDERBY-1435. Maybe we need to update the summary forDERBY-1435to something more general.