DEV Community

Pranav Bakare
Pranav Bakare

Posted on • Edited on

1 1 2 1 1

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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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.

AWS Security LIVE!

Join us for AWS Security LIVE!

Discover the future of cloud security. Tune in live for trends, tips, and solutions from AWS and AWS Partners.

Learn More

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay