Oracle Articles Blog

A Database Trigger is a stored procedure that is fired when a DML operation is performed on the table.

Sytax for creating a trigger:

CREATE OR REPLACE TRIGGER <TRIGGERNAME> before / after
[INSERT / UPDATE / DELTE ] ON <TABLE NAME>
{For each Statement / Row}
{When <condition…..>}

Types of Triggers:

Before
After
For each Row
For each Statement (default)
Instead of Trigger: This trigger is defined on a view rather than a table.


System Triggers: A new feature of Oracle8i, wherein the trigger is fired when the database startup / shutdown process.

Schema Triggers: These triggers are fired whenever a DDL statement is executed.  (Creation or Deletion of any DB Objects)

Order of  Trigger Firing:

·         Before Statement trigger (If present)

·         Each row affected by the statement

(a)    Execute row level trigger (If present)

(b)   Execute the statement itself

(c)    Execute the after row level trigger (If Present)

·         After statement trigger (If Present)