Title: Note on Speeding up Triggers

Author: Shravan Miriyala, a Senior Oracle DBA (Oracle Certified Professional), for Command
Technologies, Inc., in Englewood, Colorado.

A trigger's PL/SQL code is compiled each time it is fired (before its execution). This may slow down transaction rates,
especially if the trigger's body is large. To improve the performance, create a stored procedure that will do what the trigger is
supposed to perform, and call that stored procedure in the trigger (Remember DO NOT include any COMMIT or
ROLLBACK statements in the procedure).

Since the stored procedure is compiled during creation, the trigger only has to compile a small amount of code during its
execution and improves transaction speeds significantly for triggers with hundreds of lines of PL/SQL code.

Additional comment from  Tony Carter, Chief Technology Officer for Information Access Technologies, Inc. in
Atlanta, Georgia.

This coment regarding  "A trigger's PL/SQL code is compiled each time it is fired (before its execution)" is correct only if you're using a version of Oracle older than 7.3, or if the trigger is inval
id. Oracle 7.3 PL/SQL 2.3 does support precompiled PL/SQL trigger code.

Here is an example:

Slower Trigger:
CREATE OR REPLACE TRIGGER calculate_summaries
AFTER INSERT OF checked_out_qty ON parts_inventory
FOR EACH ROW
BEGIN
     UPDATE parts_summary
     SET total_checked_out = total_checked_out +
                              :new.checked_out_qty -
                              :old.checked_out_qty
END;

Faster Trigger:
CREATE OR REPLACE TRIGGER calculate_summaries
AFTER INSERT OF checked_out_qty ON parts_inventory
FOR EACH ROW
BEGIN
      update_summary_table(:new.checked_out_qty, :old.checked_out_qty);
END;

CREATE OR REPLACE PROCEDURE update_summary_table
(new_qty IN NUMBER, old_qty IN NUMBER) IS
BEGIN
    UPDATE parts_summary
    SET total_checked_out = total_checked_out +
                              new_qty -
                              old_qty;
END;