CODEWITHSUNDEEP
postgresqltriggers
koala
koala

Reputation: 1564

PostgreSQL not triggering when NEW.field is NULL

I have the following trigger on my table of customers so I can track whether the customer name has been changed over time and what the previous versions of the name were.

CREATE OR REPLACE FUNCTION fn_customer_changes_log_history() RETURNS trigger as 
$BODY$
    BEGIN
       IF (NEW.name <> OLD.name)  
    THEN
        INSERT INTO tbl_customers_history(customer_id, name, action)
        VALUES(OLD.id, OLD.name, 'UPDATE');
    END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER tr_customer_changes_log_history
BEFORE UPDATE ON tbl_customers
FOR EACH ROW
EXECUTE PROCEDURE fn_customer_changes_log_history();

But when I do an UPDATE when the NEW.name = NULL and and the OLD.name = "Customer 1" this trigger is not triggered? It only triggers when NEW.name has a actual string value here.

Why is that? NULL and "Customer 1" are not equal so it should trigger?

Upvotes: 2

Views: 4650

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246918

Use

IF (NEW.name IS DISTINCT FROM OLD.name)

instead of

IF (NEW.name <> OLD.name)

Upvotes: 9

redneb
redneb

Reputation: 23870

Try changing the condition to

IF COALESCE((NEW.name <> OLD.name), true)

The problem is that if NEW.field is NULL then NEW.name <> OLD.name evaluates to NULL no matter what the value of OLD.name is. In a conditional, NULL is always treated as false.

A more correct answer would have been

IF COALESCE((NEW.name <> OLD.name), true) AND NOT (NEW.name IS NULL AND OLD.name IS NULL)

to take care of the case when both NEW.name and OLD.name are NULL, so that they are not treated as equal in that case.

Upvotes: 2

Boris Schegolev
Boris Schegolev

Reputation: 3701

NULL <> 'anything' always evaluates to FALSE. Just like NULL = 'anything' or NULL > 5.

You need coalesce to fix the comparison:

COALESCE(NEW.name, '') <> COALESCE(OLD.name, '')

Upvotes: 1

Related Questions