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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
>
