CODEWITHSUNDEEP
sqlpostgresqlsql-updatedatabase-trigger
JosephL
JosephL

Reputation: 13

PgSQL - trigger to track value changes

I am trying to write a trigger function in PgSQL for tracking value changes in TableA (trigger after update on TableA). The track record will be stored in TableB.

CREATE TABLE TableA (
  ID SERIAL NOT NULL,
  ANumber integer NOT NULL,
  ADate date  NOT NULL,
  ABoolean boolean NOT NULL
);

-- table for tracking changes
CREATE TABLE TableB (
  ID SERIAL NOT NULL,
  Description varchar(100) NOT NULL
);

The body of trigger procedure should be like following...

    IF NEW.ANumber <> OLD.ANumber then
     INSERT INTO TableB (Description)
      VALUES (CONCAT('The value ', OLD.ANumber, ' changed to ', NEW.ANumber));
     RETURN NEW;
    END IF;
    
   IF NEW.ABoolean <> OLD.ABoolean then
    INSERT INTO TableB (Description)
     VALUES (CONCAT('The value changed to ', NEW.ABoolean ));
    RETURN NEW;
   END IF;

I have found in PgSQL documentation that I need to create trigger like this

CREATE TRIGGER log_changes
AFTER UPDATE ON TableA
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_changes();

The problem is if I change multiple columns in TableA. There is only one new record in TableB corresponding to the first column where the value change happend.

Is there any way to solve this?

Upvotes: 1

Views: 153

Answers (1)

kofemann
kofemann

Reputation: 4423

Take the return statement out of IF block, like this:

IF NEW.ANumber <> OLD.ANumber then
 INSERT INTO TableB (Description)
  VALUES (CONCAT('The value ', OLD.ANumber, ' changed to ', NEW.ANumber));    
END IF;

IF NEW.ABoolean <> OLD.ABoolean then
  INSERT INTO TableB (Description)
    VALUES (CONCAT('The value changed to ', NEW.ABoolean ));
END IF;
RETURN NEW;

Upvotes: 2

Related Questions