[
https://issues.apache.org/jira/browse/DERBY-5044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13083915#comment-13083915
]
Mamta A. Satoor commented on DERBY-5044:
----------------------------------------
Hi Dag, I looked through the code for trigger recompilation and found that we
only drops the dependencies related to the trigger action's stored prepared
statement and dependency between the trigger and trigger action sps. The
trigger's dependency on various privileges do not get dropped. This is because
these privilege dependencies get dropped when drop() method is called on the
TriggerDescriptor. As part of the trigger recompilation, we do not call
TriggerDescriptor.drop. Instead, we call
DataDictionary.dropTriggerDescriptor(trd, tc), we recompile the trigger action
sps and then call DataDictionary.addDescriptor(trd, sd,
DataDictionary.SYSTRIGGERS_CATALOG_NUM, false, tc). So, even though the trigger
gets recompiled as internal trigger, the trigger's dependency on various
privileges do not get lost. Those privileges were added as part of the original
create trigger sql in
CreateTriggerConstant.storeViewTriggerDependenciesOnPrivileges and do not get
touched during the recompile process. Hope this answers your question.
I will make sure that I write tests showing that privilege requirement didn't
get dropped.
> 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
>
> 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
