Creating Database Triggers
Trigger types
DML triggers combine a timing (BEFORE, AFTER, INSTEAD OF) with a level (row or statement). INSTEAD OF exists only on views; system triggers fire on schema or database events (CREATE, DROP, LOGON). A statement trigger fires once per statement even if zero rows match; a row trigger fires once per affected row.
:NEW and :OLD
In a row trigger, :NEW.column is the incoming value and :OLD.column the existing one. :OLD is NULL in an INSERT and :NEW is NULL in a DELETE. In a BEFORE row trigger you may assign to :NEW.col to change what is written; in an AFTER row trigger you cannot. In the WHEN clause the colon is omitted: WHEN (NEW.sal > OLD.sal), and UPDATING('SAL') tests which column changed.
Mutating tables
A row trigger that queries or modifies its own table raises ORA-04091. Workarounds include a compound trigger (11g), a statement trigger plus package collection state, or an autonomous transaction - noting that an autonomous transaction cannot see the parent's uncommitted changes.
Restrictions
Trigger bodies cannot contain COMMIT or ROLLBACK (except within an autonomous transaction) and should be thin. The firing order of multiple triggers on the same event is not guaranteed unless one declares FOLLOWS another_trigger (11g).
Rule of thumb: put business logic in a stored procedure and let the trigger be a one-line call, so it stays testable and small.