bulk upload and trigger

Question

Few of questions for bulk-bind and trigger (Oracle 10g)

1) Will row level trigger execute in case of bulk binding ?
2) If yes then, is there any option to surpress the execution only for bulk binding ?
3) If no then, is there a way to execute row level trigger in bulk binding ?
4) Will performance hamper in case row level trigger executes for bulk binding ?

Answer 1

Triggers are still enabled and fired when bulk-bind inserts are performed. There is nothing intinsic you can do to stop that, but of course you can put your own logic in the trigger and the code that does the bulk insert like as follows...

In a package specification:

create or replace package my_packags is

    in_bulk_mode boolean default false;

    ... -- rest of package spec
end;

In the trigger:

begin
    if NOT my_package.in_bulk_mode then
        -- do the trigger stuff
    end if;
end;

In the calling code:

my_package.in_bulk_mode := true;
-- do the bulk insert
my_package.in_bulk_mode := false;

Answer 2

Triggers execute within the SQL engine. Bulk-binding impacts the way that the calling language (pl/sql or any OCI language) calls the SQL engine, by reducing the number of calls/statements, but should not bypass any triggers.

(Imagine you have used a trigger to add validation, logging or other constraint to a database, but a third-party application would bypass it simply through using a bulk operation - this would be a recipe for data corruption and security issues).

Your statement level trigger should fire once.

You could 'disable' your trigger by making it check an in-memory session variable before doing anything else, and explicitly setting it before a bulk operation.

Row level triggers would still fire on a per-row basis, which could have a lot more impact.