Derby
  1. Derby
  2. DERBY-6

Trigger of the form: create trigger ... values myFunction(); has no effect.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.0.2.0
    • Component/s: SQL
    • Labels:
      None

      Description

      Reporting for Jack Klebanoff, filed on derby-dev list.

      > Jack Klebanoff wrote:
      >
      > | I would like to submit a fix for a bug in triggers.
      > |
      > | The bug is that a trigger of the form:
      > | create trigger ... values myFunction();
      > | has no effect. MyFunction is not called even if the trigger is fired.
      > | Side effects of myFunction do not happen. Derby does not allow a "CALL"
      > | statement inside a trigger action so a values statement is the only way
      > | to call a function/procedure in a trigger action.
      > |
      > | The cause of the bug is that since the values are not actually used by
      > | the trigger, the trigger code does not fetch the row(s) returned by the
      > | trigger action. The fix is simple: change class
      > | org.apache.derby.impl.sql.execute.GenericTriggerExecutor to fetch (and
      > | discard) the rows returned by a trigger action.
      > |
      > | Please review the change. The diff file is attached.
      >
      >
      > I think you need to close the ResultSet (rs). Other locations in the
      > code where a ResultSet is processed terminate with an rs.close(). Eg.
      > see DeleteCascadeResultSet, ConstraintConstantAction.
      >
      > Dan.
      >
      I changed the code to close the ResultSet. The diff file is attached.

      Jack
      Index: java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
      ===================================================================
      — java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java (revision 37092)
      +++ java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java (working copy)
      @@ -157,7 +157,18 @@
      */
      try
      {

      • ps.execute(spsActivation, false, false, false);
        + ResultSet rs = ps.execute(spsActivation, false, false, false);
        + if( rs.returnsRows())
        +
        Unknown macro: {+ // Fetch all the data to ensure that functions in the select list or values statement will+ // be evaluated and side effects will happen. Why else would the trigger action return+ // rows, but for side effects?+ // The result set was opened in ps.execute()+ while( rs.getNextRow() != null)+ { + }+ }

        + rs.close();
        }
        catch (StandardException e)
        {

        Activity

        Gavin made changes -
        Workflow jira [ 37480 ] Default workflow, editable Closed status [ 12797322 ]
        Jack Klebanoff made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Andrew McIntyre made changes -
        Field Original Value New Value
        Resolution Fixed [ 1 ]
        Status Open [ 1 ] Resolved [ 5 ]
        Hide
        Andrew McIntyre added a comment -

        svn commit 53829

        Show
        Andrew McIntyre added a comment - svn commit 53829
        Tulika Agrawal created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Tulika Agrawal
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development