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.

Solution

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

Comments

[Lukasz Szozda]

Could you show your code?

[hansvb]

OLD.score IS NULL might be useful. Or you could have two separate triggers (that can still share the same procedure).

[Artemiy StagnantIce Alexeew]

but according documentation postgres not garant execute operation order. For example OLD.score may be executed before OLD is null

[Artemiy StagnantIce Alexeew]

INSERT trigger's WHEN condition cannot reference OLD values LINE 34: ...EW.score IS NOT NULL AND NEW.score <> '' AND (OLD.score. I need seperate on two trigger.

[Shubham Batra]

try to define when condition inside the mytrigger()

[percy]

Remember that NULL is not equal to NULL - so when comparing OLD.score with NEW.score it would be more wise to use IS DISTINCT FROM statement: OLD.score IS DISTINCT FROM NEW.score

[Elad]

@voytech I just copied his condition. We don't know what type score is. It can also be not null field. We can work only with the information that was provided :) In any case, I edited the code with reference to your comment.

[Artemiy StagnantIce Alexeew]

But i want use went outside trigger for optimize

[Elad]

@Artemiy StagnantIce Alexeew See Option B