Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (herse.apache.org: 32.97.110.153 is neither permitted
 nor denied by domain of Stan.Bradbury@gmail.com)
Message-ID: <4672DE41.9010300@gmail.com>
Date: Fri, 15 Jun 2007 11:45:21 -0700
From: Stanley Bradbury <Stan.Bradbury@gmail.com>
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: After Insert statement trigger bug
References: 
 <OF35F22E79.AFA35861-ON862572FA.00696181-852572FA.006EFC5A@us.ibm.com>
In-Reply-To: 
 <OF35F22E79.AFA35861-ON862572FA.00696181-852572FA.006EFC5A@us.ibm.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?