You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rajesh Kartha (JIRA)" <de...@db.apache.org> on 2006/02/24 01:14:39 UTC
[jira] Created: (DERBY-1043) Create triggers in some cases do not
throw Exception when actual table names (of the trigged table) are
referenced in the Triggered-SQL-statement
Create triggers in some cases do not throw Exception when actual table names (of the trigged table) are referenced in the Triggered-SQL-statement
-------------------------------------------------------------------------------------------------------------------------------------------------
Key: DERBY-1043
URL: http://issues.apache.org/jira/browse/DERBY-1043
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.0
Reporter: Rajesh Kartha
Priority: Minor
Recently there was a question on the derby-user list about a trigger not firing correctly.
http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
ij version 10.2
ij> run 'trigger_error.sql';
ij> CONNECT 'jdbc:derby:bdb;create=true';
ij> DROP TABLE A_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
ij> DROP TABLE B_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--CORRECT BEHAVIOUR:
--==================
--This trigger statement throws an exception, since actual
--table reference cannot be made in the 'Triggered-SQL-Statement'
--
--Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
= A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
rs within a join specification and is outside the scope of the join specificatio
n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
--
--Drop and Re-create the B_TABLE, but with A_COL as the column name
--
DROP TABLE B_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--INCORRECT BEHAVIOUR:
--====================
--This trigger statement executes successfully, does NOT throw an exception, even
--when actual table reference is made in the 'Triggered-SQL-Statement'
--
--Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
--(same name as the column in A_TABLE)
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
= A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- insert data
--
insert into a_table(a_col) values ('apples');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('watermelons');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('oranges');
1 row inserted/updated/deleted
ij> insert into b_table values('tree fruit','apples',1);
1 row inserted/updated/deleted
ij> insert into b_table values('citrus fruit','oranges',1);
1 row inserted/updated/deleted
ij> insert into b_table values('melon fruit','watermelons',1);
1 row inserted/updated/deleted
--
--get contents of tables;
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |apples
2 |watermelons
3 |oranges
3 rows selected
ij> SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
--
--update a col in a_table, trigger will not fire
--
update a_table set a_col='cherries' where a_col='apples';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |oranges
3 rows selected
--
--trigger did not fire and will see the same data. 'apples' still shown in b_table;
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
the actual table name.
--
-- Attempt to create using the correct trigger statement - the RIGHT WAY
--
DROP TRIGGER UPDATE_A_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- update
--
UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |limes
3 rows selected
--
--trigger fired and reflects in the b_table data 'oranges' became 'limes'
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |limes |1
melon fruit |watermelons |1
3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Fernanda Pizzorno (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Fernanda Pizzorno updated DERBY-1043:
-------------------------------------
Attachment: derby-1043.diff
derby-1043.stat
I tried a similar update statement as the one in the trigger to see if the problem was related to the fact that it was a trigger or not, and it turns out that no exception was thrown during the execution of the update statement.
ij> -- this update should throw an exception
UPDATE B_TABLE SET B_TABLE.A_COL = A_TABLE.A_COL WHERE A_COL = 'apples';
1 row inserted/updated/deleted
In the UpdateNode.bind() method there is a call to the scrubResultColumns() method that removes the table name for the result columns. Once A_TABLE is removed, A_TABLE.A_COL becomes A_COL which is a column of B_TABLE and no exception is thrown.
The attached patch (derby-1043.diff) fixes this problem by replacing the scrubResultColumns() method by a new methods called checkTableNameAndScrubResultColumns(), which will check that the table name is valid before removing it.
I have successfully run derbyall with this patch. Can someone please review it?
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Fernanda Pizzorno
> Attachments: derby-1043.diff, derby-1043.stat, trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Rajesh Kartha updated DERBY-1043:
---------------------------------
Summary: Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table (was: Create triggers in some cases do not throw Exception when actual table names (of the trigged table) are referenced in the Triggered-SQL-statement)
A more clearer summary
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Attachments: trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1043) Create triggers in some cases do not
throw Exception when actual table names (of the trigged table) are
referenced in the Triggered-SQL-statement
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=comments#action_12367611 ]
Daniel John Debrunner commented on DERBY-1043:
----------------------------------------------
Possible more precise summary/title for this bug is:
Invalid column references are not caught in a trigger action statement when the referencing table of the column is the trigger's table.
The description is correct, but I feel the title isn't capturing the problem correctly.
> Create triggers in some cases do not throw Exception when actual table names (of the trigged table) are referenced in the Triggered-SQL-statement
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Attachments: trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1043) Create triggers in some cases do not
throw Exception when actual table names (of the trigged table) are
referenced in the Triggered-SQL-statement
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Rajesh Kartha updated DERBY-1043:
---------------------------------
Description:
Recently there was a question on the derby-user list about a trigger not firing correctly.
http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
ij version 10.2
ij> run 'trigger_error.sql';
ij> CONNECT 'jdbc:derby:bdb;create=true';
ij> DROP TABLE A_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
ij> DROP TABLE B_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--CORRECT BEHAVIOUR:
--==================
--This trigger statement throws an exception, since actual
--table reference cannot be made in the 'Triggered-SQL-Statement'
--
--Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
= A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
rs within a join specification and is outside the scope of the join specificatio
n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
--
--Drop and Re-create the B_TABLE, but with A_COL as the column name
--
DROP TABLE B_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--INCORRECT BEHAVIOUR:
--====================
--This trigger statement executes successfully, does NOT throw an exception, even
--when actual table reference is made in the 'Triggered-SQL-Statement'
--
--Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
--(same name as the column in A_TABLE)
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
= A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- insert data
--
insert into a_table(a_col) values ('apples');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('watermelons');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('oranges');
1 row inserted/updated/deleted
ij> insert into b_table values('tree fruit','apples',1);
1 row inserted/updated/deleted
ij> insert into b_table values('citrus fruit','oranges',1);
1 row inserted/updated/deleted
ij> insert into b_table values('melon fruit','watermelons',1);
1 row inserted/updated/deleted
--
--get contents of tables;
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |apples
2 |watermelons
3 |oranges
3 rows selected
ij> SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
--
--update a col in a_table, trigger will not fire
--
update a_table set a_col='cherries' where a_col='apples';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |oranges
3 rows selected
--
--trigger did not fire and will see the same data. 'apples' still shown in b_table;
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
the actual table name.
--
-- Attempt to create using the correct trigger statement - the RIGHT WAY
--
DROP TRIGGER UPDATE_A_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- update
--
UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |limes
3 rows selected
--
--trigger fired and reflects in the b_table data 'oranges' became 'limes'
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |limes |1
melon fruit |watermelons |1
3 rows selected
was:
Recently there was a question on the derby-user list about a trigger not firing correctly.
http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
ij version 10.2
ij> run 'trigger_error.sql';
ij> CONNECT 'jdbc:derby:bdb;create=true';
ij> DROP TABLE A_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
ij> DROP TABLE B_TABLE;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--CORRECT BEHAVIOUR:
--==================
--This trigger statement throws an exception, since actual
--table reference cannot be made in the 'Triggered-SQL-Statement'
--
--Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
= A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
rs within a join specification and is outside the scope of the join specificatio
n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
--
--Drop and Re-create the B_TABLE, but with A_COL as the column name
--
DROP TABLE B_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
0 rows inserted/updated/deleted
--
--INCORRECT BEHAVIOUR:
--====================
--This trigger statement executes successfully, does NOT throw an exception, even
--when actual table reference is made in the 'Triggered-SQL-Statement'
--
--Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
--(same name as the column in A_TABLE)
--
CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
= A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- insert data
--
insert into a_table(a_col) values ('apples');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('watermelons');
1 row inserted/updated/deleted
ij> insert into a_table(a_col) values ('oranges');
1 row inserted/updated/deleted
ij> insert into b_table values('tree fruit','apples',1);
1 row inserted/updated/deleted
ij> insert into b_table values('citrus fruit','oranges',1);
1 row inserted/updated/deleted
ij> insert into b_table values('melon fruit','watermelons',1);
1 row inserted/updated/deleted
--
--get contents of tables;
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |apples
2 |watermelons
3 |oranges
3 rows selected
ij> SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
--
--update a col in a_table, trigger will not fire
--
update a_table set a_col='cherries' where a_col='apples';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |oranges
3 rows selected
--
--trigger did not fire and will see the same data. 'apples' still shown in b_table;
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |oranges |1
melon fruit |watermelons |1
3 rows selected
In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
the actual table name.
--
-- Attempt to create using the correct trigger statement - the RIGHT WAY
--
DROP TRIGGER UPDATE_A_TABLE;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
0 rows inserted/updated/deleted
--
-- update
--
UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
1 row inserted/updated/deleted
--
--select from a_table
--
SELECT * FROM A_TABLE;
ID |A_COL
----------------------
1 |cherries
2 |watermelons
3 |limes
3 rows selected
--
--trigger fired and reflects in the b_table data 'oranges' became 'limes'
--
SELECT * FROM B_TABLE;
TYPE |A_COL |AMOUNT
--------------------------------------
tree fruit |apples |1
citrus fruit |limes |1
melon fruit |watermelons |1
3 rows selected
Priority: Major (was: Minor)
Set the right priority based on the description.
> Create triggers in some cases do not throw Exception when actual table names (of the trigged table) are referenced in the Triggered-SQL-statement
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Attachments: trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1043) Create triggers in some cases do not
throw Exception when actual table names (of the trigged table) are
referenced in the Triggered-SQL-statement
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Rajesh Kartha updated DERBY-1043:
---------------------------------
Attachment: trigger_error.sql
To reproduce, run the attached file thru 'ij'
> Create triggers in some cases do not throw Exception when actual table names (of the trigged table) are referenced in the Triggered-SQL-statement
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Priority: Minor
> Attachments: trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Fernanda Pizzorno (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Fernanda Pizzorno updated DERBY-1043:
-------------------------------------
Derby Info: [Patch Available]
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Fernanda Pizzorno
> Attachments: derby-1043.diff, derby-1043.stat, trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Andreas Korneliussen resolved DERBY-1043:
-----------------------------------------
Fix Version: 10.2.0.0
Resolution: Fixed
Derby Info: (was: [Patch Available])
Committed revision 418933.
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Fernanda Pizzorno
> Fix For: 10.2.0.0
> Attachments: derby-1043.diff, derby-1043.stat, trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=comments#action_12418628 ]
Andreas Korneliussen commented on DERBY-1043:
---------------------------------------------
The patch looks good to me. I intent to commit it after running derbyall.
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Fernanda Pizzorno
> Attachments: derby-1043.diff, derby-1043.stat, trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1043) Invalid column references are not
caught in a trigger action statement when the referencing table of the
column is the triggered table
Posted by "Fernanda Pizzorno (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1043?page=all ]
Fernanda Pizzorno reassigned DERBY-1043:
----------------------------------------
Assign To: Fernanda Pizzorno
> Invalid column references are not caught in a trigger action statement when the referencing table of the column is the triggered table
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1043
> URL: http://issues.apache.org/jira/browse/DERBY-1043
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Fernanda Pizzorno
> Attachments: trigger_error.sql
>
> Recently there was a question on the derby-user list about a trigger not firing correctly.
> http://article.gmane.org/gmane.comp.apache.db.derby.user/3246
> The correct approach was suggested since the trigger statement referenced the actual table name in the 'Triggered-SQL-Statement. But I was surprised that no exception was thrown in the first place during trigger creation. I tried a couple of scenarios and it turns out that if the col names of the triggered column and the column being updated in the triggered SQL are the same, Derby does not throw any exception and the trigger gets created just fine, but will not fire.
> The solution may be to throw ERROR 42X04 exception in all the cases when actual table names are referred in the triggered SQL, but the current way is misleading hence marking this issue a Medium. To reproduce the issue, simply run the attached sql script.
> ij version 10.2
> ij> run 'trigger_error.sql';
> ij> CONNECT 'jdbc:derby:bdb;create=true';
> ij> DROP TABLE A_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A_TABLE' because it does not exist.
> ij> DROP TABLE B_TABLE;
> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'B_TABLE' because it does not exist.
> ij> CREATE TABLE A_TABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY);
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, B_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --CORRECT BEHAVIOUR:
> --==================
> --This trigger statement throws an exception, since actual
> --table reference cannot be made in the 'Triggered-SQL-Statement'
> --
> --Note:The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is B_COL.
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.B_COL
> = A_TABLE.A_COL WHERE B_TABLE.B_COL = PREVIOUSROW.A_COL;
> ERROR 42X04: Column 'A_COL' is either not in any table in the FROM list or appea
> rs within a join specification and is outside the scope of the join specificatio
> n or appears in a HAVING clause and is not in the GROUP BY list. If this is a CR
> EATE or ALTER TABLE statement then 'A_COL' is not a column in the target table.
> --
> --Drop and Re-create the B_TABLE, but with A_COL as the column name
> --
> DROP TABLE B_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE B_TABLE (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0);
> 0 rows inserted/updated/deleted
> --
> --INCORRECT BEHAVIOUR:
> --====================
> --This trigger statement executes successfully, does NOT throw an exception, even
> --when actual table reference is made in the 'Triggered-SQL-Statement'
> --
> --Note: The Col name used in the 'Triggered-SQL-Statement' for B_TABLE is A_COL
> --(same name as the column in A_TABLE)
> --
> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING OLD
> AS PREVIOUSROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SET B_TABLE.A_COL
> = A_TABLE.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- insert data
> --
> insert into a_table(a_col) values ('apples');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('watermelons');
> 1 row inserted/updated/deleted
> ij> insert into a_table(a_col) values ('oranges');
> 1 row inserted/updated/deleted
> ij> insert into b_table values('tree fruit','apples',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('citrus fruit','oranges',1);
> 1 row inserted/updated/deleted
> ij> insert into b_table values('melon fruit','watermelons',1);
> 1 row inserted/updated/deleted
> --
> --get contents of tables;
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |apples
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> ij> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> --
> --update a col in a_table, trigger will not fire
> --
> update a_table set a_col='cherries' where a_col='apples';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |oranges
> 3 rows selected
> --
> --trigger did not fire and will see the same data. 'apples' still shown in b_table;
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |oranges |1
> melon fruit |watermelons |1
> 3 rows selected
> In any case, the correct way to create the trigger is to use 'NEW' to refer the new row value rather than using
> the actual table name.
> --
> -- Attempt to create using the correct trigger statement - the RIGHT WAY
> --
> DROP TRIGGER UPDATE_A_TABLE;
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER UPDATE_A_TABLE AFTER UPDATE OF A_COL ON A_TABLE REFERENCING
> OLD AS PREVIOUSROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE B_TABLE SE
> T B_TABLE.A_COL = NEWROW.A_COL WHERE B_TABLE.A_COL = PREVIOUSROW.A_COL;
> 0 rows inserted/updated/deleted
> --
> -- update
> --
> UPDATE A_TABLE SET A_COL='limes' WHERE A_COL='oranges';
> 1 row inserted/updated/deleted
> --
> --select from a_table
> --
> SELECT * FROM A_TABLE;
> ID |A_COL
> ----------------------
> 1 |cherries
> 2 |watermelons
> 3 |limes
> 3 rows selected
> --
> --trigger fired and reflects in the b_table data 'oranges' became 'limes'
> --
> SELECT * FROM B_TABLE;
> TYPE |A_COL |AMOUNT
> --------------------------------------
> tree fruit |apples |1
> citrus fruit |limes |1
> melon fruit |watermelons |1
> 3 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira