db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (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 Fri, 08 Jul 2011 01:38:16 GMT

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

Dag H. Wanvik commented on DERBY-5120:
--------------------------------------

Yes Mamta, I can see that the update statement would be the one failing with the current implementation
(if the dependency of the action on the trigger table had not been dropped, as you point out).
I think the behavior may be wrong, though, the time to fail or drop the trigger should probably
at ALTER TABLE time. But that's another issue as you say. Meanwhile, I'm still curious why
the dependency wouldn't/coudln't be added back when we reprepare, though, since the trigger
action does contain reference to the trigger table, albeit in the form of the "old"/"oldrow"
aliases (in your second example repro).

Rick, in your explanation of extended (data) dependencies, some dependencies are bi-directional,
mirroring a one-to-one relationhip. When would such bidirectional dependencies ever be required
in SYSDEPENDS? I think in some cases we only register unidirectional ones? Is this "pragmatic",
in the sense that we register deps in SYSDEPENDS (or in memory only) only in the (inverse)
direction we want to invalidate?

> 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