[jira] [Commented] (DERBY-534) Support use of the WHEN clause in CREATE TRIGGER statements

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-534?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13817656#comment-13817656 ] 

Rick Hillegas commented on DERBY-534:
-------------------------------------

I have written some tests of the WHEN clause and it seems to behave as specified. I have verified that the WHEN clause can not be used if you soft-upgrade to 10.11. Additional tests are indicated in the script attached at the end of this comment.

The WHEN clause widens the weird behavior of the DROP FUNCTION command. That behavior is neither CASCADE nor RESTRICT. Functions can be mentioned in a triggered action. If you drop the function, you'll get a confusing error the next time you execute a statement which fires the trigger. The message complains about a missing function. Now this weird error message can arise because functions can be mentioned in WHEN clauses too. I think it's fine that the weird behavior is consistent regardless of where the function is mentioned in the trigger definition. The real pity is that DROP FUNCTION is neither CASCADE nor RESTRICT.

connect 'jdbc:derby:memory:db;create=true';

create table t1( a int, b generated always as ( -a ) );
create table t2( a int );

create procedure dumpStack()
language java parameter style java no sql
external name 'java.lang.Thread.dumpStack';

create function squareRoot( val double ) returns double
language java parameter style java no sql
external name 'java.lang.Math.sqrt';

create derby aggregate mode_05 for int
external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate';

-- fails as expected because statement triggers can't reference columns
create trigger t1_trig
after insert on t1
referencing new as new
when ( squareRoot( new.a ) < 3 )
insert into t2( a ) values ( 0 );

-- fails because aggregates not allowed in WHEN clauses
create trigger t1_trig
after insert on t1
referencing new as new
for each row
when ( max( new.a ) between 0 and 3 )
insert into t2( a ) values ( new.a );
create trigger t1_trig
after insert on t1
referencing new as new
for each row
when ( mode_05( new.a ) between 0 and 3 )
insert into t2( a ) values ( new.a );

-- fails because of reference to generated column
create trigger t1_trig
no cascade before insert on t1
referencing new as new
for each row
when ( new.a > new.b )
call dumpStack();

create trigger t1_trig100
after insert on t1
referencing new as new
for each row
when ( new.a in ( values ( 1 ), ( 10 ) ) )
insert into t2( a ) values ( new.a );

create trigger t1_trig101
after insert on t1
referencing new as new
for each row
when ( new.a in ( select a from t2 ) )
insert into t2( a ) values ( new.a );

create trigger t1_trig102
after insert on t1
referencing new as new
for each row
when ( new.a in ( select a from t1 ) )
insert into t2( a ) values ( new.a );

create trigger t1_trig104
no cascade before update on t1
referencing old as old new as new
for each row
when ( new.a in ( select a from t1 where a = new.a ) )
call dumpStack();

create trigger t1_square_root
after insert on t1
referencing new as new
for each row
when ( squareRoot( new.a ) between 0 and 3 )
insert into t2( a ) values ( new.a );

insert into t1( a ) values ( 1 ), ( 10 );

select * from t1;
select * from t2;

-- dumps one stack trace as expected
update t1 set a = 10 * a;

select triggername from sys.systriggers trig, sys.systables tab
where tab.tablename = 'T1' and tab.tableid = trig.tableid;

drop function squareRoot;

select triggername from sys.systriggers trig, sys.systables tab
where tab.tablename = 'T1' and tab.tableid = trig.tableid;

insert into t1( a ) values ( 2 );


> 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
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_11
>         Attachments: WhenClause.html, WhenClause.html, WhenClause.html, WhenClause.html, WhenClause.html, derby-534-01-a-syntax.diff, derby-534-02-a-refactor.diff, derby-534-03-a-npe-testcase.diff, derby-534-04-a-referencing.diff, derby-534-05-a-generated-cols.diff, derby-534-06-a-temptables.diff, derby-534-07-a-more-restrictions.diff, derby-534-08-a-test-invalidation.diff, derby-534-09-a-drop-column-deps.diff, derby-534-09-b-drop-column-deps.diff, derby-534-10-a-get-sps.diff, derby-534-11-a-more-tests.diff, derby-534-12-a-subquery-npe.diff, derby-534-13-a-dblook.diff, derby-534-14-a-remove-formatable-upgrade.diff, parser.diff
>
>
> 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 was sent by Atlassian JIRA
(v6.1#6144)