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] [Updated] (DERBY-5044) ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped
Date Mon, 29 Aug 2011 19:57:39 GMT

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

Mamta A. Satoor updated DERBY-5044:
-----------------------------------

    Attachment: DERBY_5044_diff_patch1.txt

I am attaching a patch(DERBY5044_diff_patch1.txt) for review. This patch implements the logic
explained on August 9th 2011. To reiterate,for the table being altered, we will go through
the dependency system to determine all the triggers that depend on the table being altered(this
will include triggers defined directly on the table being altered and the triggers defined
on other tables but using the table being altered in their trigger action plan.) This is done
by first finding all the objects that depend on the table being altered. We are only interested
in SPSDescriptors from that list of dependent objects. For each of these dependent SPSDescriptor,
we want to find if they are defined for a trigger action SPS. If yes, then the trigger must
be dependent on the table being altered. For each of these dependent triggers, we dropped
their trigger descriptor from datadictionary, regenerate and rebind it's trigger action SPS
and then add the trigger descriptor(with upto date version of internal representation of trigger
action) back to datadictionary.During the rebind of trigger action, we will get exception
if the trigger depends on the column being altered. If so, then if the alter table drop column
is being done in restrict mode, then we will throw an exception that column can't be dropped
because it has dependent object. If the drop column was issued in cascade mode, then we will
drop the dependent triggers.

Please let me know if you have any comments on the attached patch. I do want to point out
that in this patch, I have removed the code which used to go directly through all the triggers
defined on the table being altered and dropping, rebinding and recreating them. This is because
the new code going through the dependency system should find all the triggers which would
be impacted by drop column, no matter whether the triggers are defined on the table being
altered or triggers defined on other tables but using table being altered in their trigger
action.) DERBY-5120 could have prevented us from catching all the triggers defined on the
table being altered through the dependency system because of missing dependency between trigger
action sps and trigger table but that has been fixed in 10.9 and 10.8 so we should be fine.
I have run all the existing junit suites and derbyall with this patch and they ran fine.


I will work next on writing test cases. 


> 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: 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>         Attachments: DERBY_5044_diff_patch1.txt
>
>
> 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

       

Mime
View raw message