PostgreSQL Triggers and Event Triggers

Q: What is TG_TAG in an event trigger?

TG_TAG is the DDL command label that fired the event, e.g. CREATE TABLE , ALTER TABLE , DROP TABLE . You can filter with WHEN tag IN (...).

1. What You'll Learn

CREATE TRIGGER (BEFORE / AFTER / INSTEAD OF)
Row-level triggers vs statement-level triggers
NEW / OLD record variables
Conditional triggers (WHEN clause)
Trigger execution order (alphabetical by name)
INSTEAD OF triggers (on views)
Event triggers (DDL events: CREATE/ALTER/DROP TABLE)
Enable/disable triggers
Triggers vs application logic

2. The Story

Alice is a database architect at an e-commerce platform. She needs to implement two core automation logics:

Automatic stock deduction: when a new order is inserted into order_items, a BEFORE INSERT trigger automatically deducts products.stock_qty, and rejects the insert if stock is insufficient.
Price audit log: when a product price is updated, an AFTER UPDATE trigger records the old and new prices into the price_audit_log table.

Alice chooses to implement these with triggers, ensuring the business rules are enforced consistently no matter which application writes the data.

3. Concept: Trigger Basics

(1) Trigger Type Overview

Timing	Row-level (FOR EACH ROW)	Statement-level (FOR EACH STATEMENT)
BEFORE	Can modify NEW, reject operation	No NEW/OLD, can validate/prepare
AFTER	Can read NEW/OLD, log	Good for summary stats
INSTEAD OF	Views only, replaces original op	Not applicable to statement-level

▶ Example: Create a Basic BEFORE INSERT Row-Level Trigger

SQL

CREATE OR REPLACE FUNCTION fn_before_order_item()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.created_at := NOW();
  NEW.line_total := NEW.quantity * NEW.unit_price;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_before_order_item
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_before_order_item();

TEXT

INSERT 0 1

▶ Example: AFTER UPDATE Audit Log Trigger

SQL

CREATE OR REPLACE FUNCTION fn_audit_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.unit_price IS DISTINCT FROM OLD.unit_price THEN
    INSERT INTO price_audit_log
      (product_id, old_price, new_price, changed_by, changed_at)
    VALUES
      (NEW.product_id, OLD.unit_price, NEW.unit_price,
       CURRENT_USER, NOW());
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_price
  AFTER UPDATE OF unit_price ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_audit_price_change();

TEXT

INSERT 0 1

(2) NEW and OLD Variables

Timing	NEW	OLD	Modifiable
BEFORE INSERT	Yes (row to be inserted)	No	Can modify NEW
BEFORE UPDATE	Yes (new value)	Yes (old value)	Can modify NEW
BEFORE DELETE	No	Yes (row to be deleted)	Not modifiable
AFTER INSERT	Yes (read-only)	No	Not modifiable
AFTER UPDATE	Yes (read-only)	Yes (read-only)	Not modifiable

▶ Example: BEFORE UPDATE Modifies NEW

SQL

CREATE OR REPLACE FUNCTION fn_auto_update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_auto_update_timestamp();

TEXT

CREATE TABLE

4. Concept: Row-Level vs Statement-Level Triggers

(1) Execution Frequency Differences

Dimension	FOR EACH ROW	FOR EACH STATEMENT
Executions	Once per affected row	Once per SQL statement
NEW/OLD	Available	Not available
Performance impact	Scales with affected rows	Fixed overhead
Typical use	Validation, column calc, cascade	Summary stats, cache refresh

▶ Example: Statement-Level Trigger Refreshes Summary

SQL

CREATE OR REPLACE FUNCTION fn_refresh_order_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_stats;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_refresh_order_stats
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION fn_refresh_order_stats();

TEXT

INSERT 0 1

▶ Example: Row-Level Trigger Validates Stock

SQL

CREATE OR REPLACE FUNCTION fn_check_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock INT;
BEGIN
  SELECT stock_qty INTO v_stock
  FROM products
  WHERE product_id = NEW.product_id;

  IF v_stock < NEW.quantity THEN
    RAISE EXCEPTION 'Insufficient stock: product % has % units, requested %',
      NEW.product_id, v_stock, NEW.quantity;
  END IF;

  UPDATE products
  SET stock_qty = stock_qty - NEW.quantity
  WHERE product_id = NEW.product_id;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_check_stock
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_check_stock();

TEXT

INSERT 0 1

5. Concept: Conditional Triggers and Execution Order

(1) WHEN Clause

The WHEN clause makes a trigger execute only when a condition is met, reducing unnecessary invocation overhead.

▶ Example: WHEN Conditional Trigger

SQL

CREATE OR REPLACE FUNCTION fn_log_big_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO big_order_log (order_id, total_amount, created_at)
  VALUES (NEW.order_id, NEW.total_amount, NOW());
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_big_order
  AFTER INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.total_amount >= 5000)
  EXECUTE FUNCTION fn_log_big_order();

TEXT

INSERT 0 1

(2) Trigger Execution Order Rules

Rule	Description
BEFORE before AFTER	All BEFORE execute before any AFTER
Same timing, alphabetical by name	`trg_a` runs before `trg_b`
INSTEAD OF replaces original op	Views only, doesn't run original INSERT/UPDATE/DELETE
BEFORE returns NULL blocks op	For UPDATE/INSERT, returning NULL skips that row

▶ Example: Execution Order of Multiple Triggers

SQL

-- These triggers execute in alphabetical order: a -> b -> c
CREATE TRIGGER trg_a_validate
  BEFORE INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_validate_order();

CREATE TRIGGER trg_b_calc_tax
  BEFORE INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_calc_order_tax();

CREATE TRIGGER trg_c_notify
  AFTER INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_notify_new_order();

TEXT

INSERT 0 1

6. Concept: INSTEAD OF Triggers

(1) INSTEAD OF on Views

A view itself doesn't support direct INSERT/UPDATE/DELETE; an INSTEAD OF trigger intercepts the operation and customizes the execution logic.

Property	Description
Views only	Can't use INSTEAD OF on tables
Must be FOR EACH ROW	Statement-level not supported
Replaces original op	Default behavior doesn't run
Good for updatable views	Maps view writes to base tables

▶ Example: INSTEAD OF INSERT into a View

SQL

CREATE VIEW vw_customer_orders AS
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  o.order_id,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

CREATE OR REPLACE FUNCTION fn_insert_customer_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customers (first_name, last_name)
  VALUES (NEW.first_name, NEW.last_name)
  ON CONFLICT DO NOTHING;

  INSERT INTO orders (customer_id, total_amount, order_date)
  SELECT customer_id, NEW.total_amount, CURRENT_DATE
  FROM customers
  WHERE first_name = NEW.first_name AND last_name = NEW.last_name;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_insert_customer_order
  INSTEAD OF INSERT ON vw_customer_orders
  FOR EACH ROW
  EXECUTE FUNCTION fn_insert_customer_order();

TEXT

INSERT 0 1

▶ Example: INSTEAD OF UPDATE into a View

SQL

CREATE OR REPLACE FUNCTION fn_update_customer_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE customers
  SET first_name = NEW.first_name,
      last_name = NEW.last_name
  WHERE customer_id = NEW.customer_id;

  UPDATE orders
  SET total_amount = NEW.total_amount
  WHERE order_id = NEW.order_id;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_customer_order
  INSTEAD OF UPDATE ON vw_customer_orders
  FOR EACH ROW
  EXECUTE FUNCTION fn_update_customer_order();

TEXT

CREATE TABLE

7. Concept: Event Triggers

(1) DDL Event Triggers (a PG Specialty)

Event triggers fire on DDL commands (CREATE/ALTER/DROP), independent of any specific table.

Event	Timing
`ddl_command_start`	Before DDL executes
`ddl_command_end`	After DDL executes
`sql_drop`	Before DROP command executes
`table_rewrite`	Before table rewrite (e.g., ALTER TYPE)

▶ Example: Event Trigger That Forbids DROP TABLE

SQL

CREATE OR REPLACE FUNCTION fn_block_drop_table()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'DROP TABLE is not allowed in production!';
END;
$$;

CREATE EVENT TRIGGER etg_block_drop
  ON sql_drop
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION fn_block_drop_table();

TEXT

CREATE TABLE

▶ Example: DDL Audit Log

SQL

CREATE TABLE ddl_audit_log (
  id SERIAL PRIMARY KEY,
  event_type TEXT,
  tag TEXT,
  object_type TEXT,
  object_name TEXT,
  command_text TEXT,
  current_user TEXT,
  event_time TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_log_ddl()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_obj RECORD;
BEGIN
  v_obj := NULL;
  INSERT INTO ddl_audit_log (event_type, tag, object_type, object_name, current_user)
  VALUES (TG_EVENT, TG_TAG,
          v_obj.object_type, v_obj.object_identity,
          CURRENT_USER);
  RAISE NOTICE 'DDL logged: % %', TG_EVENT, TG_TAG;
END;
$$;

CREATE EVENT TRIGGER etg_log_ddl
  ON ddl_command_end
  EXECUTE FUNCTION fn_log_ddl();

TEXT

INSERT 0 1

(2) Event Triggers vs Table Triggers

Dimension	Table trigger	Event trigger
Bound object	Specific table/view	Global DDL events
DML/DDL	DML (INSERT/UPDATE/DELETE)	DDL (CREATE/ALTER/DROP)
NEW/OLD	Available	None
TG_TAG	None	Yes (label like DROP TABLE)
Typical use	Validation, audit, cascade	DDL audit, security control

8. Concept: Enable/Disable and Triggers vs Application Logic

(1) Enable/Disable Triggers

Command	Effect
`ALTER TABLE t DISABLE TRIGGER trg_name;`	Disable a specific trigger
`ALTER TABLE t ENABLE TRIGGER trg_name;`	Enable a specific trigger
`ALTER TABLE t DISABLE TRIGGER ALL;`	Disable all triggers
`ALTER TABLE t ENABLE TRIGGER ALL;`	Enable all triggers

▶ Example: Disable Triggers for Bulk Import

SQL

-- Disable triggers for bulk import performance
ALTER TABLE products DISABLE TRIGGER ALL;

COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/products_bulk.csv' WITH (FORMAT csv, HEADER true);

-- Re-enable after import
ALTER TABLE products ENABLE TRIGGER ALL;

TEXT

-- SQL statement executed successfully

(2) Triggers vs Application Logic

Dimension	Trigger	Application logic
Consistency	Fires for any write path	Depends on app honoring rules
Debug difficulty	Implicit, hard to trace	Explicit call, easy to debug
Performance	Per-row extra overhead	Can be batched/optimized
Portability	PG-specific syntax	General-purpose language
Use case	Enforce invariants, audit, cascade	Complex flows, cross-system

▶ Example: Trigger Enforces Data Consistency

SQL

-- Enforce: order total must always equal sum of line items
CREATE OR REPLACE FUNCTION fn_enforce_order_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_calculated NUMERIC;
BEGIN
  SELECT COALESCE(SUM(line_total), 0) INTO v_calculated
  FROM order_items
  WHERE order_id = NEW.order_id;

  IF v_calculated IS DISTINCT FROM (
    SELECT total_amount FROM orders WHERE order_id = NEW.order_id
  ) THEN
    UPDATE orders SET total_amount = v_calculated
    WHERE order_id = NEW.order_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_enforce_order_total
  AFTER INSERT OR UPDATE ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_enforce_order_total();

TEXT

  result  
----------
   42.50
(1 row)

9. Flowchart: Trigger Selection Decision

flowchart TD
    A[Need automation logic?] --> B{DML or DDL?}
    B -->|DDL| C[Event trigger<br/>EVENT TRIGGER]
    B -->|DML| D{Table or view?}
    D -->|View| E[INSTEAD OF trigger<br/>FOR EACH ROW]
    D -->|Table| F{Modify data before op?}
    F -->|Yes| G[BEFORE trigger<br/>can modify NEW]
    F -->|No| H{Need log/cascade?}
    H -->|Yes| I[AFTER trigger<br/>can read NEW/OLD]
    H -->|No| J[No trigger needed]
    G --> K{Per row or per SQL?}
    I --> K
    K -->|Per row| L[FOR EACH ROW]
    K -->|Per SQL| M[FOR EACH STATEMENT]
    L --> N{Need condition?}
    N -->|Yes| O[WHEN clause]
    N -->|No| P[No condition]

    style C fill:#fff9c4
    style E fill:#e1bee7
    style G fill:#c8e6c9
    style I fill:#bbdefb

10. Comprehensive Example

Alice's e-commerce trigger scheme—stock deduction + price audit + auto-update timestamp:

SQL

-- 1. Auto-update timestamp on product changes
CREATE OR REPLACE FUNCTION fn_products_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_timestamp
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_products_timestamp();

-- 2. Decrease stock on order item insert, reject if insufficient
CREATE OR REPLACE FUNCTION fn_decrease_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock INT;
BEGIN
  SELECT stock_qty INTO v_stock FROM products
  WHERE product_id = NEW.product_id FOR UPDATE;

  IF v_stock IS NULL THEN
    RAISE EXCEPTION 'Product % not found', NEW.product_id;
  ELSIF v_stock < NEW.quantity THEN
    RAISE EXCEPTION 'Insufficient stock: product % (stock=%, requested=%)',
      NEW.product_id, v_stock, NEW.quantity;
  END IF;

  UPDATE products SET stock_qty = stock_qty - NEW.quantity
  WHERE product_id = NEW.product_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_decrease_stock
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_decrease_stock();

-- 3. Audit log when price changes
CREATE OR REPLACE FUNCTION fn_price_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.unit_price IS DISTINCT FROM OLD.unit_price THEN
    INSERT INTO price_audit_log
      (product_id, old_price, new_price, changed_by, changed_at)
    VALUES
      (NEW.product_id, OLD.unit_price, NEW.unit_price,
       CURRENT_USER, NOW());
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_audit
  AFTER UPDATE OF unit_price ON products
  FOR EACH ROW
  WHEN (OLD.unit_price IS DISTINCT FROM NEW.unit_price)
  EXECUTE FUNCTION fn_price_audit();

❓ FAQ

Q What happens if a BEFORE trigger returns NULL?

A For INSERT/UPDATE, returning NULL means skip that row's operation (no actual write and no subsequent triggers run). It has no effect for DELETE. Note that an AFTER trigger's return value is ignored.

Q If a table has multiple triggers at the same timing, how is order decided?

A PostgreSQL executes them in alphabetical order by trigger name—e.g., trg_a before trg_b. All BEFORE triggers run before any AFTER triggers.

Q Can a trigger execute COMMIT?

A A normal table trigger can't execute COMMIT/ROLLBACK (it runs inside a transaction). For autonomous transactions, use dblink inside the trigger function, or PG 14+'s procedural approach.

Q Can a WHEN clause condition reference other tables?

A No. A WHEN condition can only reference NEW/OLD columns—no subqueries or references to other tables. For complex conditions, check inside the trigger function body.

Q Can an INSTEAD OF trigger use FOR EACH STATEMENT?

A No. An INSTEAD OF trigger must be FOR EACH ROW, because it needs to decide per row how to map to base-table operations.

Q What is TG_TAG in an event trigger?

A TG_TAG is the DDL command label that fired the event, e.g. CREATE TABLE, ALTER TABLE, DROP TABLE. You can filter with WHEN tag IN (...).

Q Is COPY import faster after disabling triggers?

A Yes. Disabling row-level triggers during large data imports significantly improves performance, but after re-enabling you must manually handle the logic the triggers would have run (e.g., computed columns, audit records).

Q How do I avoid recursive trigger calls?

A Trigger A updating table T may fire A again. Avoid it with: a WHEN condition, a state variable (e.g., package-level variable), or switching to an AFTER trigger with a conditional check.

📖 Summary

Timing: BEFORE (can modify NEW), AFTER (log), INSTEAD OF (view mapping)
Row-level triggers run per row; statement-level run once per SQL statement
NEW holds the new value, OLD holds the old value; NEW is modifiable in the BEFORE phase
WHEN clause filters conditions, reducing unnecessary trigger invocations
Triggers at the same timing run in alphabetical name order
INSTEAD OF is for views only and must be FOR EACH ROW
Event triggers (a PG specialty) listen for DDL commands—good for audit and security control
DISABLE/ENABLE TRIGGER controls trigger state; disabling during bulk import speeds things up
Triggers guarantee data consistency but add debugging complexity—weigh carefully

📝 Exercises

⭐ Write a BEFORE UPDATE trigger function and trigger that, when the email column of the customers table is modified, automatically sets updated_at to NOW().
⭐⭐ Write an AFTER INSERT trigger that, when a new order is added to the orders table with total_amount >= 1000 USD, automatically inserts into the high_value_order_log table (recording order_id, total_amount, customer_id, created_at).
⭐⭐⭐ Create a view vw_product_sales (joining products + order_items to compute sales) and write an INSTEAD OF UPDATE trigger that maps a modified total_sold in the view back to updating products.stock_qty. Then write an event trigger that logs all CREATE TABLE and DROP TABLE DDL operations to the ddl_audit_log table.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Triggers and Event Triggers

1. What You'll Learn

2. The Story

3. Concept: Trigger Basics

(1) Trigger Type Overview

▶ Example: Create a Basic BEFORE INSERT Row-Level Trigger

▶ Example: AFTER UPDATE Audit Log Trigger

(2) NEW and OLD Variables

▶ Example: BEFORE UPDATE Modifies NEW

4. Concept: Row-Level vs Statement-Level Triggers

(1) Execution Frequency Differences

▶ Example: Statement-Level Trigger Refreshes Summary

▶ Example: Row-Level Trigger Validates Stock

5. Concept: Conditional Triggers and Execution Order

(1) WHEN Clause

▶ Example: WHEN Conditional Trigger

(2) Trigger Execution Order Rules

▶ Example: Execution Order of Multiple Triggers

6. Concept: INSTEAD OF Triggers

(1) INSTEAD OF on Views

▶ Example: INSTEAD OF INSERT into a View

▶ Example: INSTEAD OF UPDATE into a View

7. Concept: Event Triggers

(1) DDL Event Triggers (a PG Specialty)

▶ Example: Event Trigger That Forbids DROP TABLE

▶ Example: DDL Audit Log

(2) Event Triggers vs Table Triggers

8. Concept: Enable/Disable and Triggers vs Application Logic

(1) Enable/Disable Triggers

▶ Example: Disable Triggers for Bulk Import

(2) Triggers vs Application Logic

▶ Example: Trigger Enforces Data Consistency

9. Flowchart: Trigger Selection Decision

10. Comprehensive Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好