Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6783

WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.11.1.1
    • 10.12.1.1
    • SQL
    • None
    • Normal

    Description

      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;

      Attachments

        1. 6783_allTestsPass.diff
          58 kB
          Abhinav Gupta
        2. 6783_moreTests_bryan.diff
          58 kB
          Bryan Pendleton
        3. 6783_moreTests.diff
          58 kB
          Abhinav Gupta
        4. 6783_newTest_bryan.diff
          54 kB
          Bryan Pendleton
        5. 6783_newTest.diff
          52 kB
          Abhinav Gupta
        6. 6783_newTests.diff
          55 kB
          Abhinav Gupta
        7. 6783.diff
          51 kB
          Abhinav Gupta
        8. cleanedUpDiff.patch
          16 kB
          Bryan Pendleton
        9. diagnostics.diff
          9 kB
          Bryan Pendleton
        10. error-stacktrace.out
          6 kB
          Bryan Pendleton
        11. sortFunction.diff
          16 kB
          Abhinav Gupta
        12. testTriggerWhenClause.diff
          2 kB
          Abhinav Gupta
        13. workingPatch.patch
          34 kB
          Abhinav Gupta

        Issue Links

          Activity

            People

              mac777 Abhinav Gupta
              mamtas Mamta A. Satoor
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: