db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Troup <tim.tr...@ed.ac.uk>
Subject Re: Trigger behaviour on Batch Insert
Date Thu, 01 Feb 2007 23:45:46 GMT
Hi,

I should have said in my previous post that my procedure gets called  
three times which does not necessarily mean my trigger gets fired  
three times.

Tim

On 1 Feb 2007, at 22:29, Tim Troup wrote:

> Hi,
>
> I am submitting multiple update statements to the database for  
> processing as a single unit using the JDBC APIs addBatch and  
> executeBatch methods as follows:
>
> Statement stmt= dbCon.createStatement();
> stmt.addBatch("INSERT INTO bugs "+
> "VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
> stmt.addBatch("INSERT INTO bugs "+
> "VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
> stmt.addBatch("INSERT INTO bugs "+
> "VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");
>
> int[] updCnt = stmt.executeBatch();
>
> When this is executed 3 rows are added and my trigger is fired  
> three times.
>
> My trigger is defined as:
>
> CREATE TRIGGER BUGTRIG
>     AFTER INSERT ON bugs
>     REFERENCING NEW AS newbug
>     FOR EACH ROW MODE DB2SQL
>     CALL CALC_COPUB(newbug.id);
>
>
> Is this the correct behaviour? From the comment below I imagined it  
> would only be fired once.
>
> How can I change my code to ensure the trigger is only fired once  
> for a given batch?
>
> Thanks, Tim
>
>
> On 1 Feb 2007, at 03:39, Mamta Satoor wrote:
>
>> I am not too clear on the question. If your question is if a  
>> single insert statement causes 1000 rows to be inserted into a  
>> table, will the insert trigger get fired thousand times? Then the  
>> answer is no. Following is from the Reference manual for CREATE  
>> TRIGGER statement
>> A trigger defines a set of actions that are executed when a  
>> database event occurs on a specified table. A database event is a  
>> delete, insert, or update operation. For example, if you define a  
>> trigger for a delete on a particular table, the trigger's action  
>> occurs whenever someone deletes a row or rows from the table.
>>
>> Mamta
>>
>> On 1/31/07, Tim Troup <tim.troup@ed.ac.uk> wrote:
>> Hi,
>>
>> I have a trigger which is fired when data is inserted into a
>> particular table.
>> If I perform a batch insert that inserts 1000 rows will the trigger
>> be fired 1000 times or just the once?
>>
>> Thanks, Tim
>>
>


Mime
View raw message