db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex <alex3...@zoho.com>
Subject Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Date Fri, 12 Dec 2014 19:34:26 GMT
Reference Manual says that WHEN clause has no meaning in releases prior 
to 10.11:
http://db.apache.org/derby/docs/10.11/ref/index.html

------------------------------------------------------------------------

*From:* Mamta Satoor <msatoor@gmail.com>
*Sent:* Friday, December 12, 2014 10:07PM
*To:* Derby Discussion <derby-user@db.apache.org>
*Subject:* Re: Unexpected behavior of WHEN clause in CREATE TRIGGER 
statement
I get the same behavior. I will give it a try on earlier releases and 
report the results
Mamta

On Fri, Dec 12, 2014 at 1:50 AM, Alex <alex3kov@zoho.com 
<mailto:alex3kov@zoho.com>> wrote:

    Hello,
    In the below example, I expect trigger to fire and update the
    'done_date' column after update of 'status', but it doesn't. The
    database is a freshly created 10.11. Is this a bug in derby, or am I
    doing something wrong?

    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 <http://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;
    -- 
    /--Regards, Alex/


/--Regards, Alex/

Mime
View raw message