After Insert statement trigger bug

[Amir Bar-or <ba...@us.ibm.com>]

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

Re: After Insert statement trigger bug

[Stanley Bradbury <St...@gmail.com>]

Amir Bar-or wrote:
>
> 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
>
Hi -
The example code you provides shows three statements in your Batch so I 
would expect the trigger to fire three times (once for each statement 
submitted).  If you have only one statement in your batch the trigger 
should fire only once.  Are you seeing something different?