How to Create Statement Level Trigger in PostgreSQL

In PostgreSQL, create a statement level trigger by specifying FOR EACH STATEMENT in the CREATE TRIGGER command. This trigger runs once per SQL statement, not once per row affected.

Syntax

The syntax to create a statement level trigger in PostgreSQL includes the CREATE TRIGGER statement with the FOR EACH STATEMENT clause. This clause ensures the trigger fires once per SQL statement execution.

• trigger_name: Name of the trigger.
• trigger_event: Event like INSERT, UPDATE, DELETE, or TRUNCATE.
• ON table_name: Table the trigger is attached to.
• FOR EACH STATEMENT: Specifies statement level trigger.
• EXECUTE FUNCTION function_name(): Calls the trigger function.

sql

CREATE TRIGGER trigger_name
  { BEFORE | AFTER | INSTEAD OF } event [ OR ... ]
  ON table_name
  FOR EACH STATEMENT
  EXECUTE FUNCTION function_name();

Example

This example creates a statement level trigger that logs a message after any INSERT on the employees table. The trigger function uses RAISE NOTICE to show a message once per statement.

sql

CREATE OR REPLACE FUNCTION log_insert() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'An INSERT statement was executed on employees table';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_employees
  AFTER INSERT ON employees
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_insert();

Output

NOTICE:  An INSERT statement was executed on employees table

Common Pitfalls

Common mistakes include:

• Omitting FOR EACH STATEMENT which defaults to row-level triggers.
• Writing trigger functions that expect row data in statement level triggers (they do not have access to NEW or OLD records).
• Using statement level triggers when row-level triggers are needed for per-row operations.

sql

/* Wrong: Missing FOR EACH STATEMENT defaults to row-level */
CREATE TRIGGER wrong_trigger
  AFTER INSERT ON employees
  EXECUTE FUNCTION log_insert();

/* Right: Explicit statement level trigger */
CREATE TRIGGER correct_trigger
  AFTER INSERT ON employees
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_insert();

Quick Reference

Clause	Description
CREATE TRIGGER trigger_name	Defines the trigger name
BEFORE | AFTER | INSTEAD OF	When the trigger fires relative to the event
event	INSERT, UPDATE, DELETE, or TRUNCATE
ON table_name	Table the trigger is attached to
FOR EACH STATEMENT	Trigger fires once per SQL statement
EXECUTE FUNCTION function_name()	Calls the trigger function

Key Takeaways

Use FOR EACH STATEMENT to create statement level triggers in PostgreSQL.

Statement level triggers run once per SQL statement, not per row.

Trigger functions for statement level triggers cannot access row data like NEW or OLD.

Always specify FOR EACH STATEMENT explicitly to avoid default row-level triggers.

Choose statement or row level triggers based on whether you need per-statement or per-row actions.