Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 14 Nov 2013 13:47:21 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12354729.1162584436000.73582.1384436841605@arcas>
In-Reply-To: <JIRA.12354729.1162584436000@arcas>
References: <JIRA.12354729.1162584436000@arcas>
Subject: [jira] [Commented] (DERBY-2041) Trigger should register a
 dependency on tables and columns used in its body
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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)