Most triggers are row-level: they run once for every row a statement changes, with :OLD and :NEW values. A statement-level trigger runs once per statement, however many rows it changes, even none. That makes it the right tool for rules about the statement as a whole, such as forbidding deletes on a table or logging that a batch update happened.
Code for This Guide
The main examples are in the examples/triggers folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create [or replace] trigger name
{before | after} {insert | update [of column] | delete} [or ...] on table
-- no FOR EACH ROW: a statement-level trigger
begin
...
end;Without FOR EACH ROW, the trigger has no :OLD or :NEW values; it knows only which statement fired it, through the predicates INSERTING, UPDATING, and DELETING.
Forbid Deletes
Payments must never be deleted, only refunded. A BEFORE DELETE statement trigger refuses every DELETE before any row is touched.
Example:
create or replace trigger payments_no_delete_trg before delete on payments -- a statement trigger: no FOR EACH ROW begin raise_application_error(-20100, 'Payments can''t be deleted, only refunded'); end; / delete from payments where payment_id = 1;
Output:
Trigger PAYMENTS_NO_DELETE_TRG compiled Error starting at line : 7 In command - delete from payments where payment_id = 1 Error at Command Line : 7 Column : 13 Error report - SQL Error: ORA-20100: Payments can't be deleted, only refunded ORA-06512: at "NIMBUS.PAYMENTS_NO_DELETE_TRG", line 2 ORA-04088: error during execution of trigger 'NIMBUS.PAYMENTS_NO_DELETE_TRG'
The error comes from RAISE_APPLICATION_ERROR in the trigger, followed by ORA-04088, which names the trigger. The example drops the trigger afterward.
Once per Statement
Example:
-- a statement trigger fires once per statement, even when no row changes
create or replace trigger employees_upd_stmt_trg
after update on employees
begin
dbms_output.put_line('statement trigger fired');
end;
/
update employees set salary = salary where department_id = 50;
update employees set salary = salary where department_id = 999;
rollback;Output:
Trigger EMPLOYEES_UPD_STMT_TRG compiled statement trigger fired 4 rows updated. statement trigger fired 0 rows updated. Rollback complete.
The trigger fires once for the update of four rows and once for the update of none. A row-level trigger would have fired four times, then not at all. The example rolls back and drops its trigger.
Row-Level Compared with Statement-Level
| Row-level (FOR EACH ROW) | Statement-level | |
|---|---|---|
| Fires | Once per changed row | Once per statement |
| :OLD and :NEW | Available | Not available |
| May query the table | No (ORA-04091) | Yes |
| Typical use | Audit rows, derive column values | Forbid operations, log batches, check totals |
Related Guides
Conclusion
Statement-level triggers, written without FOR EACH ROW, run once per triggering statement regardless of how many rows it changes. Use them to forbid operations, log statements, or check the table as a whole, and row-level triggers when you need each row's old and new values.
