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 22:29:57 GMT
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