How to Use the CREATE TRIGGER Command in PostgreSQL

Triggers let your database react automatically to changes, without needing your application code to remember to do it every single time. Whether you want to keep an audit log, enforce a business rule that’s too complex for a simple CHECK constraint, or automatically update a timestamp, triggers are the tool for the job. In this guide, I’ll walk through the CREATE TRIGGER command in PostgreSQL in depth, covering syntax, trigger functions, timing options, practical examples, and best practices.

What Is a Trigger?

A trigger is a database object that automatically executes a specified function when a certain event happens on a table, such as an INSERT, UPDATE, DELETE, or TRUNCATE. Triggers are made up of two parts: the trigger definition itself (created with CREATE TRIGGER) and a trigger function (usually written in PL/pgSQL) that contains the actual logic to run.

This separation matters: you write the trigger function once, and then you can attach it to a trigger definition that specifies when and how it should run.

Basic Syntax of CREATE TRIGGER

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

Let’s break this down:

• trigger_name: a name for the trigger, unique within the table it’s attached to.
• BEFORE / AFTER / INSTEAD OF: when the trigger should fire relative to the event. INSTEAD OF is used specifically for views.
• event: the operation that fires the trigger — INSERT, UPDATE, DELETE, or TRUNCATE. You can combine multiple with OR.
• table_name: the table the trigger is attached to.
• FOR EACH ROW / FOR EACH STATEMENT: whether the trigger fires once per affected row, or once per SQL statement regardless of how many rows are affected.
• WHEN (condition): an optional condition that must be true for the trigger to actually execute.
• EXECUTE FUNCTION: the trigger function to run.

Writing a Trigger Function First

Before creating a trigger, you need a trigger function. Trigger functions are special: they must return type trigger, and they have access to special variables like NEW and OLD that represent the row before and after the change.

Here’s a simple trigger function that updates a last_modified column:

CREATE OR REPLACE FUNCTION update_last_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Creating a Basic Trigger

Now let’s attach this function to a table using CREATE TRIGGER:

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_last_modified_column();

This trigger fires before every UPDATE on the products table, for each row being updated, and calls the function to set the last_modified column to the current timestamp.

Understanding BEFORE vs AFTER

The timing of a trigger matters a lot for what you can do with it.

BEFORE triggers run before the actual data change happens. This is your chance to inspect or modify the row (via NEW) before it’s written, or even cancel the operation entirely by returning NULL.

CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_price_before_insert
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

AFTER triggers run after the change has already been committed to the table (within the same transaction). These are typically used for logging, auditing, or cascading changes to other tables, since the row already exists in its final form.

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_change_log (product_id, old_price, new_price, changed_at)
    VALUES (NEW.id, OLD.price, NEW.price, now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_price_after_update
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();

Notice the WHEN clause here, which ensures the logging trigger only fires when the price actually changed, not on every single update to the row.

FOR EACH ROW vs FOR EACH STATEMENT

By default, most triggers you’ll write are FOR EACH ROW, meaning the trigger function runs once for every row affected by the statement. If you update 100 rows in a single UPDATE statement, a FOR EACH ROW trigger fires 100 times.

FOR EACH STATEMENT triggers, on the other hand, fire exactly once per SQL statement, regardless of how many rows were affected. These don’t have access to NEW and OLD directly (since there could be many affected rows), but they’re useful for things like logging that a bulk operation happened, without needing per-row detail.

CREATE TRIGGER log_bulk_delete
AFTER DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_delete_event();

Using INSTEAD OF Triggers on Views

Regular views that involve joins or aggregations aren’t automatically updatable. INSTEAD OF triggers let you define custom logic for what should happen when someone tries to INSERT, UPDATE, or DELETE against such a view.

CREATE VIEW employee_department_view AS
SELECT e.id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

CREATE OR REPLACE FUNCTION handle_employee_view_update()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name, last_name = NEW.last_name
    WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_view_update
INSTEAD OF UPDATE ON employee_department_view
FOR EACH ROW
EXECUTE FUNCTION handle_employee_view_update();

Now, updates against the view get translated into the correct update against the base employees table.

Firing on Multiple Events

You can have a single trigger fire on multiple events using OR:

CREATE TRIGGER audit_changes
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_changes();

Inside the trigger function, you can check which operation triggered the call using the special TG_OP variable:

CREATE OR REPLACE FUNCTION audit_order_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_audit (order_id, action, changed_at) VALUES (NEW.id, 'INSERT', now());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_audit (order_id, action, changed_at) VALUES (NEW.id, 'UPDATE', now());
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_audit (order_id, action, changed_at) VALUES (OLD.id, 'DELETE', now());
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Conditional Triggers with WHEN

The WHEN clause lets you avoid unnecessary function calls by only firing the trigger when a specific condition is true, evaluated before the trigger function is even called. This is more efficient than checking the condition inside the function itself, especially for statement-level filtering.

CREATE TRIGGER notify_low_stock
AFTER UPDATE ON products
FOR EACH ROW
WHEN (NEW.stock_quantity < 10 AND OLD.stock_quantity >= 10)
EXECUTE FUNCTION send_low_stock_notification();

CREATE OR REPLACE TRIGGER (PostgreSQL 14+)

If you’re on PostgreSQL 14 or later, you can update an existing trigger’s definition without dropping it first:

CREATE OR REPLACE TRIGGER set_last_modified
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_last_modified_column();

On earlier versions, you’ll need to explicitly DROP TRIGGER before recreating it.

Common Use Cases for CREATE TRIGGER

1. Auditing changes: recording who changed what and when in a separate audit log table.
2. Maintaining timestamps: automatically setting created_at or last_modified columns.
3. Enforcing complex business rules: validations that go beyond what a simple CHECK constraint can express.
4. Denormalization and caching: keeping a summary or count column in sync when related rows change.
5. Cascading updates across tables: propagating a change in one table to related records in another.
6. Making views updatable: using INSTEAD OF triggers to support INSERT/UPDATE/DELETE on complex views.

Troubleshooting Common Issues

Trigger Doesn’t Seem to Fire

Double check the event type (INSERT vs UPDATE vs DELETE) matches what you’re actually doing, and confirm the WHEN clause condition, if any, isn’t silently preventing execution.

“Record NEW Is Not Assigned Yet”

This typically happens when you try to access NEW in a context where it doesn’t exist yet, like in a DELETE trigger, where only OLD is available.

Infinite Trigger Loops

If a trigger on table A updates table B, and a trigger on table B updates table A, you can end up in an infinite loop. Be very careful about triggers that write back to their own table or to tables with reciprocal triggers, and consider using conditions to break potential cycles.

Performance Degradation on Bulk Operations

FOR EACH ROW triggers execute once per row, which can slow down large bulk inserts or updates significantly. If you’re doing bulk data loads, consider disabling non-essential triggers temporarily, or redesigning heavy logic as a FOR EACH STATEMENT trigger where feasible.

Best Practices for Using CREATE TRIGGER

• Keep trigger functions small and fast: since triggers run inline with the operation that fired them, slow trigger logic slows down every INSERT, UPDATE, or DELETE.
• Use WHEN clauses to avoid unnecessary work: filtering at the trigger level is more efficient than filtering inside the function body.
• Name triggers descriptively: a name like set_last_modified is much easier to understand later than trg1.
• Document trigger side effects: since triggers execute automatically and invisibly from the application’s perspective, make sure your team knows what side effects exist.
• Avoid business-critical logic solely in triggers when possible: extremely business-critical logic hidden in triggers can be hard for new developers to discover; consider whether the logic belongs in application code, a stored procedure explicitly called, or a trigger, based on how visible it needs to be.
• Test with bulk operations: don’t just test triggers with single-row changes, make sure they behave correctly and perform acceptably with bulk inserts, updates, and deletes too.
• Watch out for recursive triggers: be deliberate about any trigger that might cause changes leading back to the same table.

Trigger Execution Order

When multiple triggers of the same type exist on a single table, PostgreSQL executes them in alphabetical order by trigger name, not in the order they were created. This is an important detail if execution order matters for your logic:

CREATE TRIGGER a_validate_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION validate_stock_availability();

CREATE TRIGGER b_calculate_total
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION calculate_line_total();

Here, prefixing trigger names with letters (a_, b_, c_, and so on) is a common convention specifically to control and make execution order explicit and predictable, rather than relying on people remembering the alphabetical rule.

Accessing Table Metadata Inside Trigger Functions

PL/pgSQL trigger functions have access to a handful of special automatic variables beyond NEW, OLD, and TG_OP. These include:

• TG_NAME: the name of the trigger that’s currently firing.
• TG_TABLE_NAME: the name of the table the trigger is defined on.
• TG_TABLE_SCHEMA: the schema of that table.
• TG_WHEN: whether the trigger fired BEFORE, AFTER, or INSTEAD OF.
• TG_LEVEL: whether it’s a ROW or STATEMENT level trigger.

This is particularly useful if you want to write one generic trigger function that’s reused across multiple tables, since the function can inspect TG_TABLE_NAME to adjust its behavior dynamically:

CREATE OR REPLACE FUNCTION generic_audit_log()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, now());
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

You can then attach this exact same function to as many tables as you like, and each invocation will correctly record which table triggered it.

Canceling an Operation from Within a Trigger

A BEFORE trigger has the power to stop the operation it’s attached to entirely, simply by returning NULL instead of NEW:

CREATE OR REPLACE FUNCTION prevent_deletion_of_locked_records()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_locked THEN
        RETURN NULL;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER block_locked_deletion
BEFORE DELETE ON contracts
FOR EACH ROW
EXECUTE FUNCTION prevent_deletion_of_locked_records();

With this in place, any attempt to delete a row where is_locked is true simply does nothing, silently, from the caller’s perspective (no rows affected), rather than raising an error. If you want the caller to see an explicit error instead, use RAISE EXCEPTION in the function body, which is often the friendlier choice since it gives clear feedback about why the operation didn’t go through.

Frequently Asked Questions

Can a trigger call another trigger indirectly?

Yes, if a trigger’s function performs an INSERT, UPDATE, or DELETE on another table that itself has triggers, those triggers will fire too. This chaining behavior is powerful but can also lead to unexpected complexity if not carefully managed, especially with deeply chained triggers across many tables.

Do triggers fire during a TRUNCATE?

Only if you explicitly define a trigger for the TRUNCATE event, and only FOR EACH STATEMENT triggers are supported for TRUNCATE, since there’s no meaningful per-row context during a full table truncation.

Can I pass arguments to a trigger function?

Yes, you can pass string literal arguments in the CREATE TRIGGER statement, and access them inside the function via the TG_ARGV array:

CREATE TRIGGER validate_column
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION validate_not_null('price');

Inside the function, TG_ARGV[0] would give you the string 'price'.

Are triggers included when I use pg_dump?

Yes, pg_dump includes trigger definitions by default as part of a full schema dump, so they’ll be recreated automatically when restoring to a new database.

Do triggers slow down every single query?

No, only the specific operations they’re attached to (INSERT, UPDATE, DELETE, or TRUNCATE). SELECT queries are never affected by triggers, since triggers only fire in response to data-modifying events.

Can I temporarily disable all triggers on a table at once?

Yes, ALTER TABLE table_name DISABLE TRIGGER ALL; disables every trigger on that table in one statement, and ALTER TABLE table_name ENABLE TRIGGER ALL; re-enables them. This is commonly used around large bulk data loads where you want to skip trigger overhead temporarily, though be cautious, since this also disables triggers that enforce foreign key constraints unless you specifically account for that.

What’s the difference between a constraint trigger and a regular trigger?

Constraint triggers, created with CREATE CONSTRAINT TRIGGER, are a special variant that can be deferred until the end of a transaction, similar to deferrable foreign key constraints. They’re less commonly used directly by application developers but are what PostgreSQL uses internally to enforce foreign key relationships behind the scenes.

Can a trigger modify the row of a different table than the one it’s attached to?

Yes, absolutely. While NEW and OLD refer specifically to the row on the table the trigger is attached to, the trigger function itself can run arbitrary SQL, including INSERT, UPDATE, or DELETE statements against completely different tables. This is exactly how cross-table auditing and denormalized summary columns are typically implemented in practice.

Do triggers work the same way on partitioned tables?

Mostly yes, though there are some nuances. In PostgreSQL, you can define FOR EACH ROW triggers directly on a partitioned parent table, and they’ll automatically apply to all its partitions. FOR EACH STATEMENT triggers, however, need to be created individually on each partition if you want statement-level behavior specific to that partition, since statement-level triggers aren’t automatically inherited the same way row-level ones are.

Wrapping Up

CREATE TRIGGER is one of PostgreSQL’s most flexible tools for building automatic, reactive behavior directly into your database layer. From simple timestamp updates to complex auditing systems and updatable views, triggers give you a lot of power. Just remember that with that power comes responsibility: keep your trigger functions efficient, be mindful of execution order and potential loops, and always document what your triggers do so future you (or your teammates) aren’t left guessing why data is changing in ways application code alone wouldn’t explain.