db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Trigger behaviour on Batch Insert
Date Fri, 02 Feb 2007 14:06:08 GMT
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
> >
>
>
>
>

Mime
View raw message