Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL trigger if one of the columns have changed

I want to keep track of changes on a table and store them in a corresponding history table after an UPDATE occurs if at least one of the fields has changed.

I have the following tables:

tbl_employees

CREATE TABLE tbl_employees (
    id serial NOT NULL,
    first_name character varying NOT NULL,
    last_name character varying NOT NULL
)

tbl_employees_history

CREATE TABLE tbl_employees (
    id serial NOT NULL,
    employee_id NOT NULL,
    first_name character varying NOT NULL,
    last_name character varying NOT NULL,
    changed_on timestamp NOT NULL
)

Function

CREATE OR REPLACE FUNCTION log_employee_changes() RETURNS trigger as 
$BODY$
BEGIN
     IF (NEW.last_name <> OLD.last_name) or (NEW.first_name <> OLD.first_name) THEN
     INSERT INTO tbl_employee_history(employee_id,last_name, first_name,changed_on)
     VALUES(OLD.id,OLD.last_name, OLD.first_name,now());
     END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Trigger

CREATE TRIGGER add_log
BEFORE UPDATE ON tbl_employees
FOR EACH ROW
EXECUTE PROCEDURE log_employee_changes();

So far so good, this thing just works. But I'm wondering if I have a table with lets say 15 columns, and I want to save a history record if one of the columns have changed. Do I have to repeat the IF statement over and over again for every field or is there some kind of trick to just check every column? That way I can just add a column to the table without the possibility to forget to change the trigger function.

Something like this:

CREATE OR REPLACE FUNCTION log_employee_changes() RETURNS trigger as 
$BODY$
BEGIN
     IF ?????there is one change on whatever column????? THEN
     INSERT INTO tbl_employee_history(employee_id,last_name, first_name,changed_on)
     VALUES(OLD.id,OLD.last_name, OLD.first_name,now());
     END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
like image 817
koala Avatar asked Sep 19 '16 14:09

koala


1 Answers

You can compare whole records:

if new <> old then ...

or

if new is distinct from old then ...

The second option is more general. Use the first one only when you are sure that the records cannot contain nulls.

like image 89
klin Avatar answered Oct 15 '22 12:10

klin