Postgres insert or update trigger WHEN condition (old)

Question

I need write insert or update trigger, but with WHEN condition with compare OLD and NEW rows.

According documentation OLD is null for insert operation. How i can use OLD in WHEN condition for INSERT AND UPDATE triggers?

Example trigger:

CREATE TRIGGER mytrigger
    BEFORE INSERT OR UPDATE ON "mytable"
    FOR EACH ROW 
    WHEN (NEW.score > 0 AND OLD.score <> NEW.score)
    EXECUTE PROCEDURE mytrigger();

but for insert OLD is null.

Answer 1

Option A:

You can change the code so that conditions will be in the trigger function rather than the trigger itself. With this approach OLD will be used only in the UPDATE.

Trigger:

CREATE TRIGGER mytrigger
    BEFORE INSERT OR UPDATE ON "mytable"
    FOR EACH ROW 
    EXECUTE PROCEDURE mytrigger();

Trigger function:

CREATE OR REPLACE FUNCTION mytrigger()
  RETURNS trigger AS
$BODY$
begin
if NEW.score > 0 then
     --code for Insert
     if  (TG_OP = 'INSERT') then
           YOUR CODE
     end if;

     --code for update
     if  (TG_OP = 'UPDATE') then
           if OLD.score <> NEW.score then  -- (if score can be null see @voytech comment to this post)
              YOUR CODE
           end if;
     end if;
end if;
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

Option B:

As Thilo suggested write two triggers that share the same trigger function.

Triggers:

CREATE TRIGGER mytrigger1
    BEFORE INSERT ON "mytable"
    FOR EACH ROW 
    WHEN NEW.score > 0
    EXECUTE PROCEDURE mytrigger();


CREATE TRIGGER mytrigger2
    BEFORE UPDATE ON "mytable"
    FOR EACH ROW 
    WHEN (NEW.score > 0 AND OLD.score <> NEW.score)
    EXECUTE PROCEDURE mytrigger();

Trigger function:

CREATE OR REPLACE FUNCTION mytrigger()
  RETURNS trigger AS
$BODY$
begin
      YOUR CODE
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE