db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-534) Support use of the WHEN clause in CREATE TRIGGER statements
Date Tue, 10 Sep 2013 15:14:51 GMT

     [ 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

Mime
View raw message