SQL OF

What is the SQL OF keyword?

The OF keyword narrows an UPDATE trigger so it fires only when specified columns are modified.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OF: PostgreSQL, Oracle, Firebird, IBM Db2. Not supported in MySQL or SQL Server.

SQL OF Full Explanation

In PostgreSQL and several other relational databases, the reserved word OF appears inside a CREATE TRIGGER statement. When you define a trigger on the UPDATE event, attaching the clause UPDATE OF column1, column2 … limits the trigger’s execution to rows where at least one of the listed columns actually changes. This selective firing avoids unnecessary trigger overhead when irrelevant columns are updated and gives developers fine-grained control over data-change reactions.OF cannot be used with INSERT, DELETE, or TRUNCATE events. It is legal only after the UPDATE keyword inside the event list. If more than one column is provided, PostgreSQL treats the list as an OR condition: the trigger executes when any listed column value differs between OLD and NEW.The feature is part of the SQL standard for column-specific triggers and is fully supported in PostgreSQL, Oracle, and Firebird. SQL Server and MySQL do not implement it natively, so developers on those platforms must add column checks inside the trigger body instead.

SQL OF Syntax

CREATE TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF }
    UPDATE OF column1 [, column2 ...]
    ON table_name
    [FOR EACH { ROW | STATEMENT }]
    EXECUTE FUNCTION function_name();

SQL OF Parameters

column1, column2 ... (identifiers) - One or more column names that, when updated, will cause the trigger to fire. None if omitted (the trigger fires on any column change).

Example Queries Using SQL OF

-- Fire only when price or stock changes
CREATE TRIGGER inventory_audit
AFTER UPDATE OF price, quantity
ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_change();

-- Attempting to use OF with INSERT raises an error
-- CREATE TRIGGER bad_trigger AFTER INSERT OF price ON products ...;

Expected Output Using SQL OF

For the first trigger, updating products
price or products
quantity invokes log_product_change(); updates to other columns do not fire the trigger

Use Cases with SQL OF

Reduce overhead by preventing triggers from running on irrelevant column changes
Maintain audit trails only for sensitive columns such as price or email
Enforce business rules tied to specific fields, e.g., quantity cannot drop below zero

Common Mistakes with SQL OF

Using OF with non-UPDATE events (causes syntax error)
Forgetting that the trigger fires when ANY listed column changes, not ALL
Assuming MySQL or SQL Server support the syntax

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

What databases support the OF clause?

PostgreSQL, Oracle, Firebird, and IBM Db2 support OF in UPDATE triggers. MySQL and SQL Server do not.

Can I list hundreds of columns after OF?

Yes, but it harms readability and maintainability. Favor a smaller, targeted list.

How do I mimic OF in SQL Server?

Create a normal UPDATE trigger and add IF UPDATE(column_name) checks inside the trigger body.

Does OF affect performance?

Yes, positively. By skipping unnecessary trigger executions you conserve CPU and I/O, especially on high-write tables.