[
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
