[
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):
{noformat}
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.
{noformat}
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: 10.3.1.4
> 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
(v6.1#6144)
