CODEWITHSUNDEEP
sqlpostgresql
chamseddine nasralli
chamseddine nasralli

Reputation: 1

why does postgresql think the record is null while its clearly not?

I have an update trigger for a table with one row, I have a column named RFID. the trigger is supposed to fire AFTER UPDATE on that row and tell me whether the value of the column changed or not. the problem is, it thinks the records OLD and NEW are null when they are quite clearly not. I changed the function code slightly to only highlight my problem. here is the function and trigger code.

    CREATE FUNCTION General_Update() RETURNS TRIGGER LANGUAGE plpgsql AS $BODY$
    DECLARE 
       data TEXT;
       BEGIN
           IF NEW."RFID" IS NULL THEN
               RAISE EXCEPTION 'RFID cannot be null';
           ELSEIF (OLD."RFID" IS DISTINCT FROM NEW."RFID") THEN
               data = 'changed';
           ELSE data = ' unchanged';
           END IF;
           PERFORM pg_notify('samme3a', data);
           RETURN NEW;
       END
       $BODY$;


       CREATE TRIGGER General_Trigger
       AFTER UPDATE ON "OPC1Data" 
       EXECUTE FUNCTION General_Update()

The exception 'RFID cannot be null' is triggered each time I attempt an update.

Upvotes: 0

Views: 117

Answers (1)

sticky bit
sticky bit

Reputation: 37472

If you want the new and old pseudo records, you want a FOR EACH ROW trigger.

CREATE TRIGGER general_trigger
               AFTER UPDATE
                     ON "OPC1Data" 
                     FOR EACH ROW
                     EXECUTE FUNCTION general_update();

And some side notes:

  • I would advise not to "hide" the actual not null constraint for "RFID" in the trigger. Just declare the column NOT NULL in the table's CREATE statement. That way everybody looking at the definition of the table will know "RFID" isn't nullable, without having to also look at the trigger.
  • Consider not to use those case sensitive identifiers in double quotes. They only make things more complicated than necessary. Identifiers in the database don't need to look "pretty". That's for the presentation layer to handle.

Upvotes: 1

Related Questions