Mamta A. Satoor created DERBY-6412:
--------------------------------------
Summary: 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
Affects Versions: 10.6.2.3
Reporter: Mamta A. Satoor
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#6144)
