Trigger on PostgreSQL updating salary column

Question

I am having some troubles setting a trigger in PostgreSQL. I have a table Employees with columns such as employee_id, salary and hourly_pay. The salary is annual, so I'd like to change the salary when I change the hourly pay.

For instance, by setting hourly pay a value, the salary must be updated to 2080 times that value. But it seems that I can't make the trigger at all. What is wrong with the following code?

create or replace function salary_update_function()
returns trigger as $salary_update$
begin
    update employees 
    set new.salary = new.hourly_pay*2080;

    return new;
end;
$salary_update$ language plpgsql;

create trigger salary_update_trigger
before update of hourly_pay on employees
for each row execute function salary_update_function();

1) Read plpgsql trigger functions 2) As written the function UPDATE will fail with something like column "new" of relation "employee" does not exist. If you where to remove the NEW's you will get a recursion as the UPDATE in the function will cause the UPDATE trigger to fire which will cause an UPDATE which cause the trigger to fire and on and on. — Adrian Klaver
– Adrian Klaver, Commented 11 hours ago
Triggers are inefficient. Why don't you just use a GENERATED AS column instead? — Charlieface
– Charlieface, Commented 9 hours ago

JPHJGA · Accepted Answer · 2025-11-30 13:04:57Z

The main issue with your code is that you are trying to run a separate SQL UPDATE statement inside the trigger function. When a BEFORE trigger runs, the row is currently "in flight." You do not need to issue a new command to update the table; instead, you simply modify the NEW record directly in memory before it gets written to the database.

CREATE OR REPLACE FUNCTION salary_update_function()
RETURNS TRIGGER AS $salary_update$
BEGIN   
    NEW.salary := NEW.hourly_pay * 2080;  
    RETURN NEW;
END;
$salary_update$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER salary_update_trigger
BEFORE UPDATE OF hourly_pay ON employees
FOR EACH ROW
EXECUTE FUNCTION salary_update_function();

Collectives™ on Stack Overflow

Trigger on PostgreSQL updating salary column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related