A statement-level trigger fires once for the entire SQL statement, regardless of how many rows the statement affects.
The key difference from a row-level trigger is:
-- Statement-level
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
...
END;
/
There is no FOR EACH ROW.
1. What is a statement-level trigger?
A statement-level trigger executes once when a DML statement occurs.
Suppose:
UPDATE employees
SET salary = salary + 1000;
If this updates 500 rows:
Statement-level trigger → fires 1 time
Row-level trigger → fires 500 times
Example
CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees table was updated');
END;
/
The trigger fires once for the entire UPDATE.
2. How do I identify a statement-level trigger?
A trigger is statement-level when it doesn't contain:
FOR EACH ROW
Statement-level
CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert statement executed');
END;
/
Row-level
CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('One row inserted');
END;
/
3. What happens if 1,000 rows are updated?
Consider:
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Trigger fired');
END;
/
Then:
UPDATE employees
SET salary = salary + 500;
If 1,000 rows are affected:
UPDATE statement
|
+---- 1,000 rows
|
↓
Statement-level trigger
|
↓
Fires ONE time
4. Can a statement-level trigger use :OLD and :NEW?
No.
This is one of the most common interview questions.
:OLD and :NEW are row-level correlation variables.
This is invalid for a statement-level trigger:
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.salary);
END;
/
You would get an error because there is no individual row associated with a statement-level trigger.
Use row-level trigger instead
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.salary);
END;
/
5. What is the difference between statement-level and row-level triggers?
| Feature | Statement-level | Row-level |
|---|---|---|
FOR EACH ROW |
❌ No | ✅ Yes |
| Fires per statement | ✅ | ❌ |
| Fires per row | ❌ | ✅ |
:OLD available |
❌ | ✅ |
:NEW available |
❌ | ✅ |
| Good for auditing individual rows | Less suitable | Good |
| Good for statement-level processing | Good | Less suitable |
Example:
UPDATE employees
SET department_id = 20
WHERE department_id = 10;
If 100 rows are affected:
Statement trigger → 1 execution
Row trigger → 100 executions
6. Can a statement-level trigger handle INSERT, UPDATE and DELETE?
Yes.
CREATE OR REPLACE TRIGGER trg_emp_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('INSERT statement executed');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('UPDATE statement executed');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('DELETE statement executed');
END IF;
END;
/
Notice that:
INSERTING
UPDATING
DELETING
can be used even though :OLD and :NEW cannot.
7. Can we use UPDATE OF with a statement-level trigger?
Yes.
CREATE OR REPLACE TRIGGER trg_salary_update
AFTER UPDATE OF salary ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary update statement executed');
END;
/
This trigger fires when salary is included in the UPDATE statement.
For example:
UPDATE employees
SET salary = salary + 1000;
fires the trigger once.
But:
UPDATE employees
SET department_id = 20;
does not fire it.
UPDATE OF salary doesn't mean Oracle checks whether the value actually changed.
For example:
UPDATE employees
SET salary = salary;
can still cause the trigger to fire.
8. Can a statement-level trigger contain WHEN?
No.
The WHEN clause is associated with row-level triggers, because it evaluates values such as NEW and OLD for individual rows.
For example:
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary > 100000)
BEGIN
...
END;
/
This is row-level.
A statement-level trigger cannot use:
WHEN (NEW.salary > 100000)
because there is no individual NEW row.
9. Can a statement-level trigger query the same table?
Generally, yes.
This is an important difference from a row-level trigger.
Example:
CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON employees
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees;
DBMS_OUTPUT.PUT_LINE(
'Employees = ' || v_count
);
END;
/
A statement-level trigger isn't in the middle of processing an individual row, so the classic mutating-table problem associated with querying the triggering table from a row-level trigger does not apply in the same way.
10. What is the mutating-table difference?
This is a popular interview question.
Row-level trigger
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees;
END;
/
This can result in:
ORA-04091: table EMPLOYEES is mutating
Statement-level trigger
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees;
END;
/
This is generally allowed because the trigger executes at the statement level.
11. Can a statement-level trigger perform DML?
Yes.
For example, suppose you want to record that the EMPLOYEES table was modified.
CREATE TABLE employee_change_log (
change_type VARCHAR2(20),
changed_by VARCHAR2(100),
changed_on TIMESTAMP
);
Trigger:
CREATE OR REPLACE TRIGGER trg_emp_change
AFTER INSERT OR UPDATE OR DELETE ON employees
BEGIN
INSERT INTO employee_change_log
VALUES (
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSTIMESTAMP
);
END;
/
If an update changes 500 rows, only one audit row is inserted.
That's exactly where a statement-level trigger can be useful.
12. Can a statement-level trigger modify :NEW?
No.
There is no :NEW in a statement-level trigger.
If you want to change a value before it is stored, use a row-level BEFORE trigger.
CREATE OR REPLACE TRIGGER trg_emp_upper
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.first_name := UPPER(:NEW.first_name);
END;
/
13. Can a statement-level trigger validate data?
Yes, but it is suited to statement-level validation, rather than individual-row validation.
For example, you might check a table-wide condition after an operation:
CREATE OR REPLACE TRIGGER trg_emp_check
AFTER UPDATE ON employees
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees
WHERE salary < 0;
IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(
-20001,
'Negative salary found'
);
END IF;
END;
/
For checking each incoming row individually, a row-level trigger is usually more appropriate.
14. Can a statement-level trigger fire for a statement that affects zero rows?
Yes.
This is a very important interview point.
Suppose:
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = -9999;
If no rows match:
Rows affected = 0
A statement-level trigger associated with UPDATE can still fire because the UPDATE statement was executed.
A row-level trigger does not fire because there were no rows to process.
Statement-level:
SQL statement executed → trigger firesRow-level:
Row affected → trigger fires
15. What happens with DELETE affecting zero rows?
Same principle.
DELETE FROM employees
WHERE employee_id = -9999;
If no rows are deleted:
Statement-level trigger → can fire
Row-level trigger → doesn't fire
16. Can a statement-level trigger commit?
No.
This is invalid:
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
COMMIT;
END;
/
It results in an error such as:
ORA-04092: cannot COMMIT in a trigger
A trigger normally participates in the same transaction as the DML that fired it.
17. Can a statement-level trigger call a procedure?
Yes.
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
log_employee_update;
END;
/
Procedure:
CREATE OR REPLACE PROCEDURE log_employee_update
IS
BEGIN
INSERT INTO employee_change_log
VALUES (
'UPDATE',
USER,
SYSTIMESTAMP
);
END;
/
This can be useful when the trigger itself should remain small.
18. What is the firing order?
For a simple DML operation, conceptually:
BEFORE STATEMENT
↓
BEFORE EACH ROW
↓
AFTER EACH ROW
↓
AFTER STATEMENT
For example, with an update:
UPDATE employees
SET salary = salary + 1000;
Oracle can execute triggers in this general sequence:
BEFORE UPDATE statement trigger
↓
BEFORE UPDATE row trigger × N rows
↓
AFTER UPDATE row trigger × N rows
↓
AFTER UPDATE statement trigger
This is an extremely useful concept for interviews.
19. Example with all four trigger timings
Suppose an update affects 3 employees.
CREATE OR REPLACE TRIGGER trg_before_stmt
BEFORE UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE STATEMENT');
END;
/
CREATE OR REPLACE TRIGGER trg_before_row
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE ROW');
END;
/
CREATE OR REPLACE TRIGGER trg_after_row
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER ROW');
END;
/
CREATE OR REPLACE TRIGGER trg_after_stmt
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT');
END;
/
For:
UPDATE employees
SET salary = salary + 1000;
Output conceptually:
BEFORE STATEMENT
BEFORE ROW
AFTER ROW
BEFORE ROW
AFTER ROW
BEFORE ROW
AFTER ROW
AFTER STATEMENT
So:
Statement triggers → once
Row triggers → once per affected row
20. What is a good real-world use case?
A very good use case is maintaining a statement-level change log.
CREATE TABLE employee_statement_log (
operation VARCHAR2(20),
username VARCHAR2(100),
operation_time TIMESTAMP
);
Trigger:
CREATE OR REPLACE TRIGGER trg_emp_statement_log
AFTER INSERT OR UPDATE OR DELETE ON employees
BEGIN
INSERT INTO employee_statement_log
(
operation,
username,
operation_time
)
VALUES
(
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSTIMESTAMP
);
END;
/
Then:
UPDATE employees
SET salary = salary + 1000;
If 2,000 employees are updated:
2,000 rows changed
↓
1 UPDATE statement
↓
1 statement-level trigger execution
↓
1 log record
21. What are common statement-level trigger interview traps?
Trap 1
Question: Does a statement-level trigger fire once per row?
Answer: ❌ No.
It fires once per SQL statement.
Trap 2
Question: Can statement-level triggers use :OLD and :NEW?
Answer: ❌ No.
Those are row-level correlation variables.
Trap 3
Question: Does a statement-level trigger fire if zero rows are affected?
Answer: ✅ Yes, the trigger can fire because the DML statement was executed.
Trap 4
Question: Does a row-level trigger fire if zero rows are affected?
Answer: ❌ No.
There are no rows for the trigger to process.
Trap 5
Question: Can a statement-level trigger query its triggering table?
Answer: ✅ Yes, generally. The classic mutating-table restriction applies to row-level access to the table being modified.
Trap 6
Question: Can a statement-level trigger modify :NEW?
Answer: ❌ No.
There is no :NEW in a statement-level trigger.
22. Statement-level vs row-level: interview summary
Memorize this:
| Feature | Statement Level | Row Level |
|---|---|---|
FOR EACH ROW |
No | Yes |
| Firing | Once / statement | Once / row |
:OLD |
No | Yes |
:NEW |
No | Yes |
WHEN |
No | Yes |
Modify :NEW |
No | Yes (BEFORE trigger) |
| Zero rows | Can fire | Doesn't fire |
| Mutating table | Generally okay | Can be a problem |
Statement-level = "Something happened to the table."
Row-level = "This particular row changed."
For example:
UPDATE employees
SET salary = salary * 1.10;
If 10,000 rows are affected:
Statement trigger: 1 time
Row trigger: 10,000 times
The key concept to understand is the difference between statement-level and row-level triggers. Once this is clear, the next important topics are compound triggers, mutating-table errors, trigger firing order, autonomous transactions, recursive triggers, and trigger execution behavior.
No comments:
Post a Comment