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 "Aleksei Kovura (JIRA)" <ji...@apache.org> on 2015/07/23 21:01:04 UTC
[jira] [Comment Edited] (DERBY-6783) WHEN clause in CREATE TRIGGER
for UPDATE is not working for the sql script below
[ https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14632738#comment-14632738 ]
Aleksei Kovura edited comment on DERBY-6783 at 7/23/15 7:00 PM:
----------------------------------------------------------------
Thanks Abhinav and Bryan. I'll try to build Derby on the specified revision and test triggers with my real world use cases as soon as I can.
Edit: It works for all my use cases!
was (Author: alex k.):
Thanks Abhinav and Bryan. I'll try to build Derby on the specified revision and test triggers with my real world cases as soon as I can.
> WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
> --------------------------------------------------------------------------------
>
> Key: DERBY-6783
> URL: https://issues.apache.org/jira/browse/DERBY-6783
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.1.1
> Reporter: Mamta A. Satoor
> Assignee: Abhinav Gupta
> Fix For: 10.12.0.0
>
> Attachments: 6783.diff, 6783_allTestsPass.diff, 6783_moreTests.diff, 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff, 6783_newTests.diff, cleanedUpDiff.patch, diagnostics.diff, error-stacktrace.out, sortFunction.diff, testTriggerWhenClause.diff, workingPatch.patch
>
>
> Following sql script was shared on derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%3c548ABA6D.8000509@zoho.com%3e).
> The UPDATE TRIGGER with the WHEN clause below does not fire as expected. Same script works fine on DB2.
> ij version 10.11
> ij> connect 'jdbc:derby:MyDbTest;create=true';
> ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1));
> 0 rows inserted/updated/deleted
> ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET done_date=current_date WHERE id=newrow.id;
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1, null, 'a');
> 1 row inserted/updated/deleted
> ij> SELECT * FROM t1;
> ID |DONE_DATE |STA&
> ---------------------------
> 1 |NULL |a
>
> 1 row selected
> ij> UPDATE t1 SET status='d';
> 1 row inserted/updated/deleted
> ij> SELECT * FROM t1;
> ID |DONE_DATE |STA&
> ---------------------------
> 1 |NULL |d
>
> 1 row selected
> ij> exit;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)