Statement Level Trigger vs Row Level Trigger

Example of a Statement-Level Trigger and its comparison to a Row-Level Trigger:

Let's use a scenario where we want to log the number of rows affected by an UPDATE operation on an employees table.

---

1. Statement-Level Trigger Example:

A Statement-Level Trigger is fired only once per SQL statement, no matter how many rows the statement affects.

CREATE OR REPLACE TRIGGER update_log_statement_trigger
AFTER UPDATE ON employees
FOR EACH STATEMENT
BEGIN
  INSERT INTO update_log (table_name, num_of_rows)
  VALUES ('employees', SQL%ROWCOUNT);
END;

• Trigger Execution: This trigger will execute once after any UPDATE operation on the employees table, even if it updates multiple rows. It logs the total number of rows affected by the UPDATE statement using SQL%ROWCOUNT.
• Scenario: If an UPDATE statement affects 5 rows, SQL%ROWCOUNT will return 5, and the log will record this in the update_log table.

---

2. Row-Level Trigger Example:

A Row-Level Trigger is fired once for each row affected by the SQL statement. It allows you to perform actions on individual rows of data.

CREATE OR REPLACE TRIGGER update_log_row_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO update_log (table_name, affected_row_id)
  VALUES ('employees', :OLD.employee_id);
END;

• Trigger Execution: This trigger will execute once for each row that is updated. In this case, it logs the employee_id of the row that was updated.
• Scenario: If the UPDATE statement affects 5 rows, the trigger will fire 5 times—once for each updated row—and each time it will log the employee_id of the row being updated.

---

In Summary:

Statement-Level Trigger: Executes once per SQL statement, regardless of the number of rows affected. It's ideal for actions that don’t need access to individual rows (like counting affected rows).

Row-Level Trigger: Executes once for each row affected, making it suitable for actions that depend on specific row values (like tracking updates to individual rows).

---

Which one to use depends on your specific requirement:

Use a Statement-Level Trigger for actions that should happen once per statement, such as logging summary information or enforcing conditions on the overall statement.

Use a Row-Level Trigger for operations that need to work with individual rows, such as auditing or data validation.