db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-2041) Trigger should register a dependency on tables and columns used in its body
Date Thu, 14 Nov 2013 13:47:21 GMT

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

Knut Anders Hatlen commented on DERBY-2041:

I've come to the conclusion that, at least in the first round, we should keep the fix minimal
and not try to improve those parts that already work. That is, DROP COLUMN, DROP DERBY AGGREGATE,
DROP TYPE and DROP SEQUENCE seem to detect dependent triggers already, so they should be left
as they are.

I've gone through the failing tests and found that they come in the following categories:
* Cleanup code that drops tables/triggers between test cases without paying attention to trigger
dependencies. These DROP statements should be reordered so that dependents are dropped before
their dependencies.
* Tests that explicitly check for the old behaviour. That is, they verify that trigger dependencies
can be dropped, and that the trigger still exists afterwards and fails when it fires. These
test cases should instead verify the new behaviour (dropping the dependency fails, and the
trigger is still functioning).
* Tests that inspect the contents of the SYS.SYSDEPENDS table, and now see more rows.

The one test failure that stood out, was the following (copied from lang/refActions.sql):

ij> create table t1( a int not null primary key , b int , c int not null unique) ;
0 rows inserted/updated/deleted
ij> create table t2( x int not null unique references t1(c) ON DELETE CASCADE ) ;
0 rows inserted/updated/deleted
ij> create table t3( y int references t2(x) ON DELETE CASCADE) ;
0 rows inserted/updated/deleted
ij> create trigger trig_delete after DELETE on t1
for each row
delete from t2;
0 rows inserted/updated/deleted
ij> drop table t3;
ERROR X0Y25: Operation 'DROP TABLE' cannot be performed on object 'T3' because TRIGGER 'TRIG_DELETE'
is dependent on that object.

The trigger 'TRIG_DELETE' doesn't reference table 'T3', so I found this error puzzling. TRIG_DELETE's
action deletes rows from T2, and possibly cascades to T3 because of T3's REFERENCING clause.
So it is true that TRIG_DELETE depends on T3. However, it's more of a soft dependency, as
its trigger action would still be compilable after T3 is dropped. So I think it wouldn't be
unreasonable to expect "drop table t3" to succeed in this case, and just have TRIG_DELETE
recompile its SPS. I'm not sure if it is possible to distinguish between these kinds of dependencies
currently, but at least there is room for improvement in this edge case.

> 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: Documentation, SQL
>    Affects Versions:
>            Reporter: Bryan Pendleton
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_11
>         Attachments: register-dependencies.diff
> 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

View raw message