Oracle 1Z0-183: Salary Cap Trigger That Only Works One Row at a Time

Salary Cap Trigger That Only Works One Row at a Time

HR has a row-level trigger trg_sal_cap on EMPLOYEES, BEFORE INSERT OR UPDATE FOR EACH ROW, enforcing a department salary budget. Its body runs SELECT SUM(salary) INTO v_sum FROM employees WHERE dept_id = :NEW.dept_id; and raises ORA-20001 if v_sum + :NEW.salary exceeds the budget for that department. Single-row changes through the HR form have worked for two years. A migration script running INSERT INTO employees SELECT ... FROM employees_stage for 300 rows fails immediately with ORA-04091 (table EMPLOYEES is mutating, trigger/function may not see it). A second attempt replaced the trigger with a BEFORE INSERT statement-level trigger; it compiled and now enforces nothing at all. The budget rule must still be enforced, including for the batch load.

1. Why does the same trigger succeed for a one-row HR-form update and fail for the 300-row INSERT ... SELECT?

Read the worked answer

The trigger queries the very table it is defined on, during row-level firing. Oracle forbids that because the table is in an indeterminate state mid-DML.

Single-row DML: the row being changed is the only affected row, so the table is not considered mutating and the read is allowed. This is why two years of form usage produced no error - the code was always wrong, the workload just never exposed it.
Multi-row DML: more than one row is affected, the table is flagged mutating for the duration of the row-level firing, and SELECT SUM(...) FROM employees inside the trigger raises ORA-04091. The same error would appear for a multi-row UPDATE that moves people between departments.

So the symptom is workload-dependent, not data-dependent. Any fix must survive multi-row DML, not just make the single-row path keep working.

2. Explain why the statement-level BEFORE INSERT trigger enforces nothing, and sketch the compound-trigger solution.

Read the worked answer

Statement-level triggers fire once per statement and have no :NEW/:OLD pseudo-records. The body can no longer see the incoming row, so the budget comparison has nothing to compare - it compiles and silently does nothing. That is worse than the original, because it is a silent loss of a control.

Compound trigger (11g+) structure - the classic fix:

CREATE OR REPLACE TRIGGER trg_sal_cap
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
  TYPE t_rows IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  g_rows t_rows;
  g_idx  PLS_INTEGER := 0;

  BEFORE EACH ROW IS
  BEGIN
    g_idx := g_idx + 1;
    g_rows(g_idx) := :NEW;      -- stash, do not query the table
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
    v_sum NUMBER;
  BEGIN
    FOR i IN 1 .. g_idx LOOP
      SELECT NVL(SUM(salary),0) INTO v_sum
        FROM employees WHERE dept_id = g_rows(i).dept_id;   -- now legal
      IF v_sum > budget(g_rows(i).dept_id) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Budget exceeded for dept '||g_rows(i).dept_id);
      END IF;
    END LOOP;
  END AFTER STATEMENT;
END;

The AFTER STATEMENT section runs when the table is no longer mutating, so the aggregate read is legal and the budget is evaluated against the fully loaded data.

3. A reviewer objects that even the compound trigger rejects the whole 300-row load if one row breaks the budget. Is the trigger the right place for this rule at all?

Read the worked answer

Partially right. A trigger is a reasonable fence for ad-hoc DML, but it is the wrong place for batch validation, for three reasons:

Cost. The AFTER STATEMENT loop does one aggregate per stashed row - 300 full scans of EMPLOYEES for a 300-row load. Aggregate once by department instead of per row, or the load time becomes the problem.
All-or-nothing granularity. A trigger can only fail the whole statement. If the business wants the 299 good rows in and the 1 bad row reported, that logic must live in the loader, not the trigger.
Ordering ambiguity. The budget check sees post-statement state, so which row "caused" the breach is not well defined; the message can only name a department.

What I would do: keep the compound trigger as the guard for OLTP paths, and have the migration script validate the staged data before inserting (a single grouped query comparing SUM(salary) per department against the budget table). Batch then fails fast with a precise report, and the trigger still stops manual misuse. Also verify the trigger handles UPDATE that changes dept_id (old and new departments both need checking).