Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <27331107.1154326573835.JavaMail.jira@brutus>
Date: Sun, 30 Jul 2006 23:16:13 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-1613) A trigger does not get invalidated
 when the view used by it is dropped
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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