CODEWITHSUNDEEP
databasepostgresqltriggers
Motheus
Motheus

Reputation: 543

PostgreSQL trigger won't inset audit value

I'm working with PostgreSQL 8.1 and I created a trigger for a table, so if something gets updated or inserted it will be also registered in a log table, but I'm having issues with NULL values:

If I update a column with a NULL value, then it won't insert any value to the log, but with char to char it will be fine, so maybe I'm missing something.

The table was created like this:

CREATE TABLE log_test(

    id_test         integer   NOT NULL DEFAULT nextval('id_test_seq'),
    type         char(3),
    product         char(10),
    table             char(15),
    field            char(10),
    old_val            char(10),
    new_val            char(10),
    user            char(10),
    event            char(10),
    date           timestamp with time zone      NOT NULL DEFAULT now(),
 CONSTRAINT "log_test_prim" PRIMARY KEY ("id_test")
);

The trigger was created like:

CREATE FUNCTION "public"."log_test_trigger" () RETURNS "trigger" AS 'BEGIN
...
   IF (TG_OP = ''UPDATE'') THEN
            IF (NEW.name <> OLD.name) THEN
                   INSERT INTO log_test (type, table, field, old_val, new_val, user, event) VALUES (NEW.type,  TG_RELNAME, ''name'', OLD.name, NEW.name, NEW.user, ''UPDATE'');
            END IF;
...
END;' LANGUAGE "plpgsql"
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

Any help?

Upvotes: 0

Views: 53

Answers (1)

klin
klin

Reputation: 121604

You cannot compare NULL to a value, i.e. both NULL = 2 and NULL <> 2 are NULL. Add ISNULL:

...
IF (TG_OP = ''UPDATE'') THEN
    IF (NEW.name <> OLD.name OR NEW.name ISNULL) THEN
        INSERT INTO log_test (type, table, field, old_val, new_val, user, event) VALUES (NEW.type,  TG_RELNAME, ''name'', OLD.name, NEW.name, NEW.user, ''UPDATE'');
    END IF;
...

or (probably better) coalesce() for both NEW and OLD records:

...
IF (TG_OP = ''UPDATE'') THEN
    IF (coalesce(NEW.name, '') <> coalesce(OLD.name, '')) THEN
        INSERT INTO log_test (type, table, field, old_val, new_val, user, event) VALUES (NEW.type,  TG_RELNAME, ''name'', OLD.name, NEW.name, NEW.user, ''UPDATE'');
    END IF;
...

Upvotes: 1

Related Questions