db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.
Date Mon, 28 Aug 2006 01:35:23 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430913 ] 
            
Yip Ng commented on DERBY-1621:
-------------------------------

Release note for this jira:

Release Note for DERBY-1621
---------------------------

PROBLEM
    Trigger action statement is not recompile when there is a change that would affect it.


SYMPTOMS

    (1)  Trigger action such as an INSERT statement does not get recompiled when the underlying
         table is affected by a CREATE or DROP INDEX statement.  e.g.: 

         create table t (i int);
         create table t2 (i int);
         create trigger tt after insert on t for each statement mode db2sql insert into t2
values 1;
         insert into t values 1;
         select * from t2;
         create unique index tu on t2(i);
         insert into t values 1;
         select * from t2;
         insert into t values 1;
         1 row inserted/updated/deleted
       
         
         The above example creates an unique index on table t2.  when the trigger is fired,
it did not 
         raise an unique constraint error.

    (2)  When the trigger action statement underlying view gets dropped, the trigger statement
did not get 
           recompiled.  e.g.:

         create table t11 (c111 int not null primary key, c112 int);
         insert into t11 values(1,1);
         insert into t11 values(2,2);
         create view v21 as select * from user1.t11;
         create table t31 (c311 int);
         create table t32 (c321 int);
         create trigger tr31t31 after insert on t31 for each statement mode db2sql insert
into t32 values (select c111 from user1.v21 where c112=1);
         insert into t31 values(1);
         select * from t31;
         select * from t32;
         drop view v21;
         insert into t31 values(1);

         In the above example, a view which the trigger action references is dropped; however,
the last SQL
         INSERT statement did not throw an error.

    (3)  Conglomerate does not exist occurs in a specific case after dropping a table referenced
by a trigger.
          The trigger action is not being recompiled and raises SQLSTATE XSAI2 even though
the table being 
          dropped was recreated again.  e.g.:

         create table t1 (id int, name varchar(20));
         create table t2 (id int);
         create trigger test_trigger after insert on t2 for each row mode db2sql insert into
t1 values(100, 'hundred');
         insert into t2 values(1);
         insert into t2 values(1);
         select * from t1;
         drop table t1;
         insert into t2 values(1);
         create table t1 (id int, name varchar(20));
         insert into t2 values(1);         

         In the above example, a table which the trigger action references is dropped.  The
last INSERT  
         statement should execute successfully but it raises SQLSTATE XSAI2: The conglomerate
(896) 
         requested does not exist.  


CAUSE
   Derby did not perform invalidation of the trigger action when object(s) that the trigger
   references are modified or dropped; hence, resulting in the stated problem above.  The

   affected versions are Derby 10.0 and 10.1.

SOLUTION
    A fix to resolve the above Derby symptoms is available in 10.2.


WORKAROUND
   None.


> Trigger action statement is not recompile when there is a change that would affect it.
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-1621
>                 URL: http://issues.apache.org/jira/browse/DERBY-1621
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.0
>            Reporter: Daniel John Debrunner
>         Assigned To: Yip Ng
>            Priority: Critical
>             Fix For: 10.2.1.0
>
>         Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, derby1621trunkdiff03.txt,
derby1621trunkdiff04.txt, derby1621trunkstat01.txt, derby1621trunkstat02.txt, derby1621trunkstat03.txt,
derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled when there
is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into by the trigger's
action statement. When the tirgger fires it does not raise any error (should raise a unique
constraint violated error) and does not insert the value into the index. A select from t2
does not show the additional rows in t2 as it is performing an index scan, once the index
is dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1
> 1
> 3 rows selected

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

        

Mime
View raw message