Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 9 Aug 2011 05:26:27 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1320879388.19093.1312867587204.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1883232320.19120.1297814217500.JavaMail.tomcat@hel.zones.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
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-5044?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1308=
1444#comment-13081444 ]=20

Mamta A. Satoor commented on DERBY-5044:
----------------------------------------

I found that given a table descriptor, we can use DataDictionary.getProvide=
rsDescriptorList to get the list of all the objects that depend on the tabl=
e descriptor. Once we have all those objects, we can look through them to f=
ind just the stored prepared statements since our ultimate goal is to find =
all the triggers who might be using the column being dropped in their trigg=
er action plans. For every dependent stored prepared statement, we will che=
ck it's dependents (same as we did for table descriptor using DataDictionar=
y.getProvidersDescriptorList). If the dependent on the stored prepared stat=
ement is a trigger then we know that the trigger action plan is using the t=
able which is getting altered. We will pick these triggers, drop and recrea=
te them after regenerating their trigger action plan and rebinding that pla=
n. If we run into error during this process, then we know that we have foun=
d a depedent trigger and hence alter table drop column restrict will fail a=
nd cascade will drop the dependent trigger. I will look into coding this an=
d post a patch soon. Please let me know if anyone has feedback on this appr=
oach. 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: 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 do=
es not get detected.
> A test case for this can be found in AlterTableTest.java
>         //Following test case involves two tables. The trigger is defined=
=20
>         //on table 1 and it uses the column from table 2 in it's trigger =
=20
>     =09//action. This dependency of the trigger on a column from another=
=20
>         //table is not detected by Derby.
>         st.executeUpdate("create table atdc_14_tab1 (a1 integer, b1 integ=
er)");
>         st.executeUpdate("create table atdc_14_tab2 (a2 integer, b2 integ=
er)");       =20
>         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 =3D newt.a1");
>         // following is not the right behavior. we should have gotten an =
error
>         // because column being dropped is getting used in a trigger acti=
on=20
>         st.executeUpdate("alter table atdc_14_tab2 drop column a2 restric=
t");
>         rs =3D
>                 st.executeQuery(
>                 " select triggername from sys.systriggers where " +
>                 "triggername =3D '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