Tim,
 
The behavior you are noticing is the correct behavior. In your eg of three insert statements in the batch, every insert statement is going to fire the insert trigger.
 
If the 3 rows were inserted using one single insert statement, then the insert trigger will fire only once. eg of that would be
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+ 
"VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'}),
(1008,'Cannot load DLL', 3,1,{d '1999-01-01'}), (1009,'Applet locks up',2,2,{d '1999-01-01'})");
int[] updCnt = stmt.executeBatch();
 
Batching in my example doesn't make sense because I am adding only one statement to the batch but I just wanted to show an example of how the trigger will fire only once for the 3 rows that would get inserted.
 
Mamta
 
On 2/1/07, Tim Troup <tim.troup@ed.ac.uk> 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