db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Assigned] (DERBY-6412) ALTER TABLE DROP COLUMN should drop statement level trigger defined at the table level
Date Wed, 27 Aug 2014 07:26:57 GMT

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

Knut Anders Hatlen reassigned DERBY-6412:
-----------------------------------------

    Assignee: Knut Anders Hatlen

> 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.4
>            Reporter: Mamta A. Satoor
>            Assignee: Knut Anders Hatlen
>             Fix For: 10.7.1.4, 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.2
>
>
> 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.2#6252)

Mime
View raw message