[
https://issues.apache.org/jira/browse/DERBY-534?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-534:
-------------------------------------
Attachment: parser.diff
Kristian mentioned in the issue description that there are traces of support
for the WHEN clause in the code. I see that there is some code for it in
CreateTriggerNode for it at least.
I experimented with the attached patch, parser.diff, that makes the parser
accept the WHEN clause and pass the information on to CreateTriggerNode.
As can be seen from the ij transcript below, this actually creates an SPS for
the WHEN clause in SYS.SYSSTATEMENTS:
{noformat}
ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true';
ij> create table t1(x int);
0 rows inserted/updated/deleted
ij> create table t2(x int);
0 rows inserted/updated/deleted
ij> create trigger tr1 after insert on t1 referencing new as new for each row
when (true) insert into t2 values 1;
0 rows inserted/updated/deleted
ij> create trigger tr2 after insert on t1 referencing new as new for each row
when (false) insert into t2 values 2;
0 rows inserted/updated/deleted
ij> select * from sys.sysstatements where stmtname like 'TRIGGER%';
STMTID |STMTNAME
|SCHEMAID |&|VALID|TEXT
|LASTCOMPILED |COMPILATIONSCHEMAID
|USINGTEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
070a00b0-0141-0866-ce24-000018892889|TRIGGERACTN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889
|80000000-00d2-b38f-4cda-000a0a412c00|T|false|insert into t2 values 1
|2013-09-10 17:04:22.812
|80000000-00d2-b38f-4cda-000a0a412c00|NULL
89e800c0-0141-0866-ce24-000018892889|TRIGGERACTN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889
|80000000-00d2-b38f-4cda-000a0a412c00|T|true |insert into t2 values 2
|2013-09-10 17:04:23.827
|80000000-00d2-b38f-4cda-000a0a412c00|NULL
8f3600b1-0141-0866-ce24-000018892889|TRIGGERWHEN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889
|80000000-00d2-b38f-4cda-000a0a412c00|T|false|VALUES true
|2013-09-10 17:04:22.798
|80000000-00d2-b38f-4cda-000a0a412c00|NULL
921800c1-0141-0866-ce24-000018892889|TRIGGERWHEN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889
|80000000-00d2-b38f-4cda-000a0a412c00|T|true |VALUES false
|2013-09-10 17:04:23.816
|80000000-00d2-b38f-4cda-000a0a412c00|NULL
4 rows selected
{noformat}
Unfortunately, it doesn't seem to be used at trigger execution time, and both
triggers get executed even if only one of them has a WHEN clause that evaluates
to TRUE:
{noformat}
ij> insert into t1 values 1;
1 row inserted/updated/deleted
ij> select * from t2;
X
-----------
1
2
2 rows selected
{noformat}
Also, the current code doesn't seem to like references to transition variables:
{noformat}
ij> create trigger tr3 after insert on t1 referencing new as new for each row
when (new.x > 40) insert into t2 values 3;
ERROR 42X04: Column 'NEW.X' is either not in any table in the FROM list or
appears within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'NEW.X' is not a column in the
target table. (errorCode = 30000)
{noformat}
> Support use of the WHEN clause in CREATE TRIGGER statements
> -----------------------------------------------------------
>
> Key: DERBY-534
> URL: https://issues.apache.org/jira/browse/DERBY-534
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Kristian Waagan
> Priority: Minor
> Labels: derby_triage10_11
> Attachments: parser.diff, WhenClause.html
>
>
> Support use of the WHEN clause in CREATE TRIGGER statements. The clause is
> described in the SQL standard (2003) in chapter "11.39 <trigger definition>"
> under "<triggered action>".
> There are traces in the code that suggests some work has been done on this
> earlier. If anyone knows something about this, please add a comment to this
> issue.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
