PL/SQL Triggers

A trigger is a stored block that runs automatically when a specific event happens to a table — an INSERT, UPDATE, or DELETE. Nobody calls it directly; the database fires it as a side effect of the triggering statement.

Timing and Level

Two independent choices shape every trigger: when it fires relative to the change, and how many times.

Choice	Options
Timing	`BEFORE` the change is written, or `AFTER` it is already committed to the block.
Level	`FOR EACH ROW` (once per affected row, can read/change that row's values) or statement-level (once total, regardless of how many rows are affected).

:NEW and :OLD

Inside a row-level trigger, :NEW is the row as it will be (or was just made) and :OLD is the row as it was before. A BEFORE trigger can assign to :NEW fields to change what actually gets written; :OLD is always read-only.

Event	:OLD	:NEW
`INSERT`	not available (nothing existed before)	the row being inserted
`UPDATE`	the row before the change	the row after the change
`DELETE`	the row being removed	not available (nothing will exist after)

Example: defaulting a column and enforcing a rule

create or replace trigger trg_products_bi
  before insert on demo_products
  for each row
begin
  if :new.created_at is null then
    :new.created_at := sysdate; -- fill in a default
  end if;

  if :new.price < 0 then
    raise_application_error(-20001, 'price cannot be negative: ' || :new.price);
  end if;
end;
/

Logging Changes

A very common use: keep an audit trail of what changed, by whom, and when — without touching the application code that issues the UPDATE. This example assumes a separate price_change_log table already exists to receive the rows.

Example:

create or replace trigger trg_products_au
  after update on demo_products
  for each row
  when (old.price != new.price)
begin
  insert into price_change_log (product_id, old_price, new_price, changed_at)
  values (:old.id, :old.price, :new.price, sysdate);
end;
/

When Not To Use a Trigger

A trigger runs invisibly on every write — heavy logic there makes ordinary INSERT/UPDATE statements silently slow, with no clue in the calling code why;
Triggers on the same table can fire each other and cause a mutating-table error (ORA-04091) if one tries to query the table it is triggered on;
Business logic hidden in a trigger is easy to forget exists — prefer an explicit call to a package procedure when the caller can reasonably be expected to call it itself.

Rule of thumb: use a trigger for things the table itself must always guarantee — a default value, a hard constraint, an audit trail — not for the primary business logic of your application.

See it in a full demo: Demo 15 — Triggers.