How to Use WHEN Conditions and Column-Specific Triggers in PostgreSQL

Triggers in postgres allow users to execute different functions automatically when operations like INSERT, UPDATE, or DELETE happen. And postgres supports different ways to control when a trigger can be executed. Two useful ones are the WHEN clause and column-specific features with UPDATE OF.

This helps you to control the trigger execution and avoid unnecessary trigger calls by keeping the function execution to only happen when specific conditions are met.

1. Trigger Firing Conditions (WHEN Clause)

The WHEN clause will help you specify a particular condition that should be satisfied in order to get the trigger to execute. Now, let’s consider an example using the employee table below.

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC,
    department VARCHAR(50)
);
INSERT INTO employee (name, salary, department)
VALUES
('John', 30000, 'IT'),
('Mary', 40000, 'HR'),
('David', 50000, 'Finance');

Here, I have added a few columns to this table; now we have to execute a trigger function only when an employee's salary is increased. First, let’s create the trigger like this:

CREATE OR REPLACE FUNCTION employee_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger executed for employee: %', NEW.name;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This trigger function raises a message whenever it is executed. Now, we have to make use of the WHEN clause so that we can check (NEW.salary > OLD.salary) to know if the salary has been increased.

CREATE TRIGGER salary_increase_trigger
AFTER UPDATE
ON employee
FOR EACH ROW
WHEN (NEW.salary > OLD.salary)
EXECUTE FUNCTION employee_trigger_func();

When an employee's salary increases, the condition becomes true, and the trigger function executes. If the salary decreases or remains unchanged, the function is skipped.

This is useful when you want to execute trigger functions only for specific data changes.

Let’s try the query below that increases the salary of a particular employee:

UPDATE employee
SET salary = 35000
WHERE id = 1;

You will see the trigger function getting executed since the salary increased; the output looks like:

NOTICE:  Trigger executed for employee: John
UPDATE 1

Let's see what happens if the condition is not met.

UPDATE employee
SET salary = 25000
WHERE id = 1;
-- OR salary not changed
UPDATE employee
SET salary = salary
WHERE id = 1;

In the first query, let's update the salary to a smaller value, and the second query keeps the salary unchanged; in both cases, the trigger function will not be executed. Example:

db_data=# UPDATE employee
SET salary = 15000
WHERE id = 1;
UPDATE 1
db_data=# UPDATE employee
SET salary = salary
WHERE id = 1;
UPDATE 1

To delete the trigger you just added, you can use the query below:

DROP TRIGGER salary_increase_trigger ON employee ;

2. Column-Specific Triggers

The UPDATE OF will help you specify the columns that must be changed (appear in an UPDATE query) to fire the trigger function. Let’s use the above table we created for employee info here:

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary
ON employee
FOR EACH ROW
EXECUTE FUNCTION employee_trigger_func();

So this trigger uses UPDATE OF and triggers the function employee_trigger_func() after an update on the salary column happens. So, here postgres checks for the column salary in any UPDATE query for the employee table. Let’s see an example:

UPDATE employee
SET salary = 35000
WHERE id = 1;

The output:

NOTICE:  Trigger executed for employee: John
UPDATE 1

You can see the trigger function executing since the salary appeared in the query; you may need not have to make a specific change to the salary column in your update query, something like this:

UPDATE employee
SET salary = salary
WHERE id = 1;

Will also fires the trigger function. It only checks whether the column was targeted by the UPDATE statement.

3. Combining UPDATE OF and WHEN

Suppose your requirement is the following:

Execute the trigger only when the salary column is updated, AND the new salary is greater than the old salary.

And you can just combine what we discussed above; the UPDATE OF and WHEN clauses together will get this, so you will have something like this:

CREATE TRIGGER salary_increase_trigger
AFTER UPDATE OF salary
ON employee
FOR EACH ROW
WHEN (NEW.salary > OLD.salary)
EXECUTE FUNCTION employee_trigger_func();

The behaviour of the above query will be like:

The WHEN clause and column-specific triggers using UPDATE OF provide better control over trigger execution in Postgres. By using these features, developers can avoid unnecessary trigger function calls and execute additional logic only when relevant changes occur.