db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: After Insert statement trigger bug
Date Fri, 15 Jun 2007 18:45:21 GMT
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?



Mime
View raw message