db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andreas Korneliussen (JIRA)" <derby-...@db.apache.org>
Subject [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
Date Fri, 30 Jun 2006 11:41:31 GMT
    [ 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


Mime
View raw message