Statement Level Trigger

Statement Triggers A statement triggers is fired once on behalf of the triggering statement, in spite of of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.               

Statement triggers are valuable if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, if a trigger makes a composite security audit record based on the type of triggering statement, a statement trigger is used.

             .  AFTER row trigger After modifying each row affected by the triggering statement and possibly applying right integrity constraints, the trigger action is executed for the current row provided the trigger constraint was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

You can have many triggers of the same type for the same statement for any given table. For example you may have two BEFORE STATEMENT triggers for  UPDATE statements on the EMP table. Multiple triggers of the same type allow modular installation of applications that have triggers on the same tables. Also, Oracle snapshot logs use AFTER ROW triggers, so you can design your own AFTER ROW trigger in addition to the Oracle-defined AFTER ROW trigger.

You can create as many triggers of the preceding different types as you need for each type of DML statement (INSERT, UPDATE, or DELETE). For example, suppose you have a table, Salary , and you want to know when the table is being accessed and the types of queries being issued. Figured 1.1 contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. A global session variable, STAT.ROWCNT, is initialized to zero by a BEFORE statement trigger, then it is increased each time the row trigger is executed, and finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger.