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