db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1613) A trigger does not get invalidated when the view used by it is dropped
Date Mon, 31 Jul 2006 06:16:13 GMT
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

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
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


View raw message