Re: [jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng]

Thanks for the info, Dan.   I briefly looked at the reported issue, I think I know what the problem is.  When Derby binds the trigger's action node (insert stmt in this case) in CreateTriggerNode, the action node bind phase creates a dependency from the compilation context but however, the context's current dependent is not the insert statement itself but of the trigger statement.  Hence, that is probably the reason why the insert statement didn't get invalidated when create/drop index is executed.

On 8/1/06, Daniel John Debrunner (JIRA) <derby-dev@db.apache.org> wrote:

    [ http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12424968 ]

Daniel John Debrunner commented on DERBY-1621:
----------------------------------------------

Thanks for looking at this Yip - note that Derby has an existing dependency system already, thus it should be possible to fix this using the existing scheme rather than inventing anything new. E.g. in my example script the select * from t2 does receive to invalidations for the create and drop indexes which allow it to be recompiled to go against the base table or index.

> Trigger action statement is not recompile when there is a change that would affect it.
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-1621
>                 URL: http://issues.apache.org/jira/browse/DERBY-1621
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.0.0
>            Reporter: Daniel John Debrunner
>         Assigned To: Yip Ng
>            Priority: Critical
>             Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into by the trigger's action statement. When the tirgger fires it does not raise any error (should raise a unique constraint violated error) and does not insert the value into the index. A select from t2 does not show the additional rows in t2 as it is performing an index scan, once the index is dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> -----------
> 1
> 1
> 1
> 3 rows selected

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira