[jira] [Updated] (DERBY-6412) ALTER TABLE DROP COLUMN should drop statement level trigger defined at the table level

["Mike Matrigali (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-6412:
----------------------------------

    Component/s: SQL

> ALTER TABLE DROP COLUMN should drop statement level trigger defined at the table level
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-6412
>                 URL: https://issues.apache.org/jira/browse/DERBY-6412
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.2.3
>            Reporter: Mamta A. Satoor
>             Fix For: 10.7.1.4, 10.8.3.1, 10.9.2.2, 10.10.1.3, 10.11.0.0
>
>
> While working on DERBY-6383, found following behavior for 10.6 codeline. The behavior is correct for 10.7 and higher. I haven't tested codelines earlier than 10.6
> Dropping a column from the trigger table should drop any statement level triggers defined a the trigger table level but I do not see that behavior in 10.6 codeline. 10.7 and higher do drop the affected triggers and that change might have happened as party of DERBY-4988(ALTER TABLE DROP COLUMN should make use of information in SYSTRIGGERS to detect column used through REFERENCING clause to find trigger dependencies). DERBY-4988 added information about columns used in trigger action to SYSTRIGGERS table's existing column REFERENCEDCOLUMNS. That information is not available in 10.6 and earlier and so in 10.6, we can't catch all the dependencies in case of DROP COLUMN. Without looking at the code, I think though that for 10.6, we might be able to catch the case of statement triggers defined at the table level since for such triggers REFERENCEDCOLUMNS will be null.
> A simple test case below which works correctly in trunk through 10.7 but not in 10.6(and probably earlier codelines). 
> java org.apache.derby.tools.ij
> connect 'jdbc:derby:db1;create=true';
> CREATE TABLE DERBY_6368_TAB1 (X INTEGER, Y INTEGER);
> CREATE TABLE DERBY_6368_TAB2 (X INTEGER, Y INTEGER);
> INSERT INTO DERBY_6368_TAB1 VALUES(1, 2);
> – statement trigger defined at table level
> CREATE TRIGGER t3 AFTER UPDATE ON DERBY_6368_TAB1 REFERENCING old table AS old INSERT INTO DERBY_6368_TAB2 SELECT * FROM old;
> --1 row
> UPDATE DERBY_6368_TAB1 SET x = x + 1;
> select count from DERBY_6368_TAB2;
> delete from DERBY_6368_TAB2;
> – following should drop the statement trigger defined above but it does not do that in 10.6
> ALTER TABLE DERBY_6368_TAB1 DROP COLUMN Y;
> ALTER TABLE DERBY_6368_TAB2 DROP COLUMN Y;
> select * from sys.systriggers;
> UPDATE DERBY_6368_TAB1 SET x = x + 1;
> --count should be 0 but in 10.6, the update fires the trigger since the trigger didn't get dropped by DROP COLUMN
> select count from DERBY_6368_TAB2;



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)