[jira] [Commented] (DERBY-2041) Trigger should register a dependency on tables and columns used in its body

["ASF subversion and git services (JIRA)" <ji...@apache.org>]

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

ASF subversion and git services commented on DERBY-2041:
--------------------------------------------------------

Commit 1617641 from [~mamtas] in branch 'code/trunk'
[ https://svn.apache.org/r1617641 ]

DERBY-6684(Failure in testDERBY5120NumRowsInSydependsForTrigger when upgrading from 10.11.1.0 to trunk)
We ran into DERBY-6684 after the fix for DERBY-2041 went into 10.11
The failure is the expected behavior since when dropping a table,
we should detect any dependent triggers defined on other tables
and drop table should fail if such dependencies exist. To fix the
test failure, we just need to switch the order of drop table
DERBY-2041(Trigger should register a dependency on tables and columns used in its body)
Drop ATDC_TAB1 before ATDC_BKUP1 because ATDC_TAB1 has a trigger
on it which references ATDC_BKUP1. If we try dropping ATDC_BKUP1
without first dropping ATDC_TAB1, there will be an error message
that trigger ATDC_TAB1_TRG1 depends on ATDC_BKUP1

> Trigger should register a dependency on tables and columns used in its body
> ---------------------------------------------------------------------------
>
>                 Key: DERBY-2041
>                 URL: https://issues.apache.org/jira/browse/DERBY-2041
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4
>            Reporter: Bryan Pendleton
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_backport_reject_10_10, derby_triage10_11
>             Fix For: 10.11.1.1
>
>         Attachments: d2041-1a.diff, d2041-1b.diff, d2041-2a-upgrade-test.diff, register-dependencies.diff, releaseNote.html, releaseNote.html
>
>
> A trigger registers a dependency in the Dependency Manager for those columns which cause the firing of the trigger, but does not register a dependency on tables and columns which are used in the body of the trigger. This means that the trigger may unexpectedly become invalid due to a change to one of those tables and columns in its body, and the user may be surprised that Derby did not warn them of this problem when the underlying table/column change was made.
> An example of this problem is as follows:
> create table t1 (c1 int);
> create table t2 (c2 int);
> create trigger tr1 after update of c1 on t1  for each row mode db2sql insert into t2 (c2) values (1);
> With this set of definitions, Derby will warn/prevent the user about changes to table t1 and column c1, but will not warn the user about changes to table t2 and column c2. If the user drops or renames t2 or c2, the trigger will then give an error the next time it fires.
> It seems like it would be an improvement for the trigger to record this dependency on table t2 and column c2.



--
This message was sent by Atlassian JIRA
(v6.2#6252)