Postgres triggers - adding old and new values

Question

I am trying to learn Postgres triggers, using some simple examples. I have created a simple table:

create table emp (empname text, salary integer, last_user text);

My goal is to replace the old salary with a new salary computed as the salary inserted (new) + the old salary. I could not get them to sum even when I did not put a condition (i.e. empname is the same)

Here is my code:

-- this table returns a new row instead of summing
CREATE OR REPLACE FUNCTION emp_stamp() RETURNS trigger
AS $emp_stamp$
BEGIN
new.salary = new.salary + old.salary ;
RETURN NEW; 
END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE  TRIGGER emp_stamp
BEFORE UPDATE on emp
FOR EACH ROW
EXECUTE PROCEDURE emp_stamp();

When I insert into the table, I get a new row added and no existing rows follow the formula:

INSERT INTO EMP VALUES('BR',39970,'BR')

I have also unsuccessfully tried the UPDATE command.

Answer 1

Your CREATE TRIGGER script says BEFORE UPDATE. So this trigger is not fired at all for INSERT commands.

Moreover, the same trigger function would raise an error for INSERT commands anyway because, obviously, there is no "old" version for newly inserted rows.

It should work just fine as is for UPDATE, though. I just cleaned it up a bit:

CREATE OR REPLACE FUNCTION emp_stamp()
  RETURNS trigger AS
$func$
BEGIN
   NEW.salary := NEW.salary + OLD.salary;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp
BEFORE UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Just a proof of concept, I fail to see the point of adding up old an new value.

For starters, your table needs a proper PRIMARY KEY, a serial column for instance (empname is hardly unique):

CREATE TABLE emp (
  emp_id serial PRIMARY KEY
, empname text
, salary integer
, last_user text);

Then the UPDATE could work reliably:

UPDATE EMP
SET    salary = 39970
WHERE  emp_id = 123;

I still don't see how the mentioned trigger would make sense. You could increase an existing salary like this, no trigger involved:

UPDATE EMP
SET    salary = salary + 39970
WHERE  emp_id = 123;