Derby
  1. Derby
  2. DERBY-1621

Trigger action statement is not recompile when there is a change that would affect it.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Urgent
    • Issue & fix info:
      Release Note Needed

      Description

      A trigger action statement, such as an INSERT statement is not recompiled when there is some DDL change on the underlying table, such as a CREATE INDEX.

      In the example below a unique index is added to the table (t2) inserted into by the trigger's action statement. When the tirgger fires it does not raise any error (should raise a unique constraint violated error) and does not insert the value into the index. A select from t2 does not show the additional rows in t2 as it is performing an index scan, once the index is dropped the rows appear to the select.

      ij version 10.2
      ij> connect 'jdbc:derby:cs;create=true';
      ij> create table t (i int);
      0 rows inserted/updated/deleted
      ij> create table t2 (i int);
      0 rows inserted/updated/deleted
      ij> create trigger tt after insert on t for each statement mode db2sql
      insert into t2 values 1;
      0 rows inserted/updated/deleted
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> create unique index tu on t2;
      0 rows inserted/updated/deleted
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> drop index tu;
      0 rows inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1
      1
      1

      3 rows selected

      1. derby1621trunkdiff01.txt
        30 kB
        Yip Ng
      2. derby1621trunkstat01.txt
        0.9 kB
        Yip Ng
      3. derby1621trunkdiff02.txt
        29 kB
        Yip Ng
      4. derby1621trunkstat02.txt
        1.0 kB
        Yip Ng
      5. derby1621trunkdiff03.txt
        31 kB
        Yip Ng
      6. derby1621trunkstat03.txt
        1.0 kB
        Yip Ng
      7. derby1621trunkdiff04.txt
        32 kB
        Yip Ng
      8. derby1621trunkstat04.txt
        1.0 kB
        Yip Ng

        Issue Links

          Activity

          Daniel John Debrunner created issue -
          Daniel John Debrunner made changes -
          Field Original Value New Value
          Link This issue blocks DERBY-1613 [ DERBY-1613 ]
          Daniel John Debrunner made changes -
          Link This issue is related to DERBY-1435 [ DERBY-1435 ]
          Yip Ng made changes -
          Assignee Yip Ng [ yipng ]
          Yip Ng made changes -
          Attachment derby1621trunkdiff01.txt [ 12338772 ]
          Attachment derby1621trunkstat01.txt [ 12338771 ]
          Yip Ng made changes -
          Derby Info [Patch Available]
          Yip Ng made changes -
          Attachment derby1621trunkstat02.txt [ 12338839 ]
          Attachment derby1621trunkdiff02.txt [ 12338840 ]
          Yip Ng made changes -
          Derby Info [Patch Available]
          Yip Ng made changes -
          Attachment derby1621trunkdiff03.txt [ 12338919 ]
          Attachment derby1621trunkstat03.txt [ 12338918 ]
          Yip Ng made changes -
          Attachment derby1621trunkstat04.txt [ 12338929 ]
          Attachment derby1621trunkdiff04.txt [ 12338930 ]
          Yip Ng made changes -
          Derby Info [Patch Available]
          Rick Hillegas made changes -
          Derby Info [Patch Available]
          Yip Ng made changes -
          Derby Info [Release Note Needed]
          Yip Ng made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Yip Ng made changes -
          Derby Info [Release Note Needed] [Existing Application Impact, Release Note Needed]
          Andrew McIntyre made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Release Note Needed, Existing Application Impact] [Release Note Needed]
          Gavin made changes -
          Workflow jira [ 12377228 ] Default workflow, editable Closed status [ 12801433 ]

            People

            • Assignee:
              Yip Ng
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development