db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5120) Row from SYSDEPENDS gets deleted when a table has update triggers defined on it and an upate is made to the table
Date Thu, 07 Jul 2011 17:47:16 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13061458#comment-13061458
] 

Mamta A. Satoor commented on DERBY-5120:
----------------------------------------

I have a test case(with comments in the script below) that shows how missing row in SYSDEPENDS
does not catch a trigger getting invalid.

connect 'jdbc:derby:db1;create=true';
create table t1(c11 int, c12 int);
insert into t1 values(1,1);
create table t1_bkup1(c111 int, c112 int);
create table t1_bkup2(c211 int, c212 int);

-- for following trigger, 5 rows will be added to sysdepends
create trigger tr1 after update of c11 on t1 referencing
 old_table as old for each statement insert into
 t1_bkup1 select * from old;

-- for following trigger, 5 rows will be added to sysdepends
-- This trigger creation will also invalidate the trigger tr1 
create trigger tr2 after update of c11 on t1 referencing
 old as oldrow for each row insert into
 t1_bkup2(c211) values (oldrow.c11);
select count(*) from sys.sysdepends;

-- following update will cause tr1 to recompile because it has been marked invalid
-- But as part of recompile, trigger action sps's dependency on trigger table will
--  be lost bringing down the total number of rows in sysdepends from 10 to 9
update t1 set c11=2;
select count(*) from sys.sysdepends;

-- Because the dependency between trigger tr1's trigger action sps and trigger table
--   is lost, following alter table on trigger table will not be noticed by tr1
alter table t1 add column c113 int;
-- following update will not fail for trigger tr1 even though it is not correct anymore(tr1
--  is inserting into t1_bkup1 but after the alter table the number of columns in 
--  trigger table do not match the number of columns in t1_bkup1 but this is never 
--  caught because of the missing dependency.)
update t1 set c11=3;

-- We can prove that trigger tr1 is incorrect after alter table because if we tried 
--  creating another trigger with same definition as tr1, it will fail
create trigger tr3 after update of c11 on t1 referencing
 old_table as old for each statement insert into
 t1_bkup1 select * from old;


> Row from SYSDEPENDS gets deleted when a table has update triggers defined on it and an
upate is made to the table
> -----------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5120
>                 URL: https://issues.apache.org/jira/browse/DERBY-5120
>             Project: Derby
>          Issue Type: Task
>          Components: SQL
>    Affects Versions: 10.2.2.0, 10.8.1.2
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>
> I have an ij script below which shows that the number of rows in SYSDEPENDS go down by
1 for the following test case after an update is made to a table with update triggers defined
on it. Am not sure what kind of problems the missing dependnecy might cause.
> connect 'jdbc:derby:c:/dellater/db1;create=true';
> CREATE TABLE ATDC_13_TAB1(c11 int, c12 int);
> insert into ATDC_13_TAB1 values (1,11);
> create table ATDC_13_TAB2(c21 int, c22 int);
> insert into ATDC_13_TAB2 values (1,11);
> create table ATDC_13_TAB3(c31 int, c32 int);
> insert into ATDC_13_TAB3 values (1,11);
> create table ATDC_13_TAB1_backup(c11 int, c12 int);
> insert into ATDC_13_TAB1_backup values (1,11);
>                 create trigger ATDC_13_TAB1_trigger_1 after update 
>                 on ATDC_13_TAB1 for each row mode db2sql 
>                 INSERT INTO ATDC_13_TAB1_BACKUP(C11) 
>                 SELECT C21 from ATDC_13_TAB2;
>                  create trigger ATDC_13_TAB1_trigger_2 after update 
>                 on ATDC_13_TAB1 for each row mode db2sql 
>                 INSERT INTO ATDC_13_TAB1_BACKUP 
>                  SELECT C31, C32 from ATDC_13_TAB3;
> -- following shows 14 rows
> select * from sys.sysdepends;
> update ATDC_13_TAB1 set c12=11;
> -- following shows only 13 rows
> I tried this on 10.2 and 10.8 and saw the same behavior on both. It seems like the dependency
that gets dropped is between the stored prepared statement and a table. Have not spent enough
time to find out more details but I thought it is worth pointing out the behavior
> select * from sys.sysdepends;

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message