Trigger on bulk insert

[souravmallik]

Hello,

Is there any way to improve performance of an after insert trigger in bulk insert.

I have a pro*c program that insert data in a stagging table in bulk insert mode. From stagging table I have to insert to the source table synchronously. So I'm inserting data through pl/sql fired by the table trigger.

But the trigger is inserting data row wise.. so nullifying the performance gained by using bulk insert.

Can any one advice how to improve the performance of the final insert.

Thanks..
Sourav

[debasisdas]

Are you using for each row in the trigger.

[souravmallik]

Are you using for each row in the trigger.

Yea I'm using FOR EACH ROW... and thats where the bottle neck is..

How to use Statement level trigger in bulk insert..

Suppose I'm inserting 1000 row in a bulk insert, then will the 1000 row insertion be called a 'statement' is the statement level trigger??

[debasisdas]

remove FOR EACH ROW from the trigger defination.

[debasisdas]

A row level trigger is defined using the clause for each row. If this clause is not given, the trigger is assumed to be a statement trigger. A row trigger executes once for each row after (before) the event.

In contrast, a statement trigger is executed once after (before) the event, independent of how many rows are affected by the event. For example, a row trigger with the event specification after update is executed once for each row affected by the update. Thus, if the update affects 20 tuples, the trigger is executed 20 times, for each row at a time. In contrast, a statement trigger is only executed once.