db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5044) ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped
Date Thu, 21 Jul 2011 04:40:58 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13068799#comment-13068799

Mamta A. Satoor commented on DERBY-5044:

I spent some time to see how we can find out all the triggers that might be using a given
table in their trigger actions. This way, when a column is getting dropped from a table, we
can detect dependent triggers. If the drop column is to be run in cascade mode, them the dependent
triggers will be dropped. If the drpo column is to be run in restrict mode, then the drop
will fail if any dependent triggers are found, 

What I found is that there is no direct way to find such triggers but we can get to them through
a little round about way. All the stored prepared statements (including the ones created for
trigger actions) record all their dependencies in SYSDEPENDS. So, if a stored prepared statement
is using 2 tables in trigger action, both those dependencies will be put in SYSDEPENDS. We
can build our logic based on this information.

Anytime a column is getting dropped, we will look at SYSDEPENDS and find all the stored prepared
statement that are using the table whose column is getting dropped. Then we will look at those
stored prepared statements in SYSDEPENDS to see if they depend on a trigger descriptor. If
yes, then we know that this stored prepared statement is for a trigger's trigger action. With
this logic, we can find all the triggers which might be using a given table in their trigger
action. Now, if the ALTER TABLE DROP COLUMN is getting run, then we will drop all these dependent
triggers, regenerate their trigger action and then recreate the triggers. During the regeneration
fo trigger action, if the trigger action is using the column being dropped, the regeneration
will fail and we will know that this trigger is using the column being dropped. With this
information, if we are running ALTER TABLE DROP COLUMN with restrict, then ALTER TABLE will
fail because we found a trigger using the column being dropped. If we are running ALTER TABLE
DROP COLUMN with cascade, then we will simply drop the trigger and not recreate it.

Please let me know if anyone has any feedback on this approach. Thanks

> ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger
action using the column being dropped
> ---------------------------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-5044
>                 URL: https://issues.apache.org/jira/browse/DERBY-5044
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:,,,,,,
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
> A trigger in it's trigger action.can use columns from a table other than the trigger
table. When such a column is dropped, the trigger dependency does not get detected.
> A test case for this can be found in AlterTableTest.java
>         //Following test case involves two tables. The trigger is defined 
>         //on table 1 and it uses the column from table 2 in it's trigger  
>     	//action. This dependency of the trigger on a column from another 
>         //table is not detected by Derby.
>         st.executeUpdate("create table atdc_14_tab1 (a1 integer, b1 integer)");
>         st.executeUpdate("create table atdc_14_tab2 (a2 integer, b2 integer)");     
>         st.executeUpdate("insert into atdc_14_tab1 values(1,11)");
>         st.executeUpdate("insert into atdc_14_tab2 values(1,11)");
>         st.executeUpdate(
>                 " create trigger atdc_14_trigger_1 after update " +
>                 "on atdc_14_tab1 REFERENCING NEW AS newt " +
>                 "for each row " +
>                 "update atdc_14_tab2 set a2 = newt.a1");
>         // following is not the right behavior. we should have gotten an error
>         // because column being dropped is getting used in a trigger action 
>         st.executeUpdate("alter table atdc_14_tab2 drop column a2 restrict");
>         rs =
>                 st.executeQuery(
>                 " select triggername from sys.systriggers where " +
>                 "triggername = 'ATDC_14_TRIGGER_1' ");
>         JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});

This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message