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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay