db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Amir Bar-or <baro...@us.ibm.com>
Subject After Insert statement trigger bug
Date Thu, 14 Jun 2007 20:12:22 GMT
Hello,


We are trying to use an After insert statement trigger  that calls a java 
function with the JDBC Batch API.  Instead of getting a single activation 
of the trigger for a whole statement , we get one for every inserted row 
like a row trigger.   We get the same bug when we use ODBC array binding. 
It seems to be related only to the java function , since a trigger that 
calls a sql statement seems to be working fine.  An example code is below. 
Is anyone aware of this?


      // create table
      String str = "Create table " + tableName + " (a integer, c 
VARCHAR(32))";
      psCreate = dbConnection.prepareStatement(str);
      psCreate.execute();
 
      // create function
      Statement stmt = dbConnection.createStatement();
      stmt.executeUpdate("CREATE FUNCTION notifyData ("
                         + ")"
                         + " RETURNS INTEGER"
                         + " LANGUAGE JAVA "
                         + " PARAMETER STYLE JAVA"
                         + " NO SQL"
                         + " EXTERNAL NAME 
'com.ibm.jStage.tests.testJDBCBatchFunctionality.catchTriggerEvent'");
 
      stmt.close();
 
      // create trigger
      str =
          "CREATE TRIGGER " + "\"trig" + tableName + "\" AFTER INSERT ON " 
+ tableName
              + " REFERENCING NEW_TABLE as NEW" 
              + " FOR EACH STATEMENT MODE DB2SQL " 
              + " VALUES( notifyData() )";
 
      stmt = dbConnection.createStatement();
      stmt.executeUpdate(str);
 
      // prepare insert statement
      PreparedStatement ps = null;
      ps = dbConnection.prepareStatement("insert into " + tableName + " 
(a,c) values (?,?)");
      ps.setInt(1, 1);
      ps.setString(2, "XXXX");
      ps.addBatch();
 
      ps.setInt(1, 2);
      ps.setString(2, "YYYY");
      ps.addBatch();
 
      ps.setInt(1, 3);
      ps.setString(2, "ZZZZZZZZ");
      ps.addBatch();
 
 
      // insert something
      ps.executeBatch();
 
      // catch event
 
      // count callbacks
 
      dbConnection.close();
      derbyServer.shutdown();

      if(count > 1)
        Assert.fail();
    }
    catch(Exception e)
    {
      Assert.fail();
    } 
  }
 
  public static int catchTriggerEvent() throws SQLException
  {
    System.out.println("Trigger activated!");
 
    count++;
 
    return 0;
  }

        // We get 3 invocation of this catchTriggerEvent() rather than 1. 
Seems like a a bug right?

Regards,
Amir Bar-or


Mime
View raw message