[ 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