CODEWITHSUNDEEP
postgresqlstored-procedurestriggers
user1044111
user1044111

Reputation: 57

Postgresql 9.2 trigger to log changes to data in another table

I have several tables and want to log when changes are made to them, what the change was and who made the change. Postgresql 9.2

CREATE TABLE unitsref (
unitsrefid serial primary key,
units varchar,
unitname varchar,
inuse boolean,
systemuse varchar,
keynotes integer,
linkid integer
);

Is the best practise to use OLD.* IS DISTINCT FROM NEW.* ?

CREATE TRIGGER log_unitsref
    AFTER UPDATE ON unitsref
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE log_unitsref();

I am only really interested in the three fields:

units varchar,
unitname varchar,
inuse boolean,

I want to record these changes in a table eventlog with the fields:

recordtype varchar,
recordkey varchar,
changetype varchar,
personid integer,
changedate date,
changetime time,
changefrom varchar,
changeto varchar,

What is the best syntax to write a function to do this? In Progress Openedge I would write

create  EventLog.
assign  EventLog.PersonId    = glb-Personid
        EventLog.RecordType  = "UnitsRef"
        EventLog.RecordKey   = UnitsRef.Units
        EventLog.ChangeType  = "Create"
        EventLog.changeFrom  = ""
        EventLog.changeTo    = ""
        EventLog.changeDate  = today
        EventLog.changeTime  = time

but I don`t know the best method in Postgresql

Upvotes: 1

Views: 2405

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656411

I am only really interested in the three fields

Then it should be more efficient to only call the trigger after changes to these fields:


CREATE TRIGGER log_unitsref
AFTER UPDATE OF units, unitname, inuse
ON unitsref
FOR EACH ROW
WHEN (OLD.units, OLD.unitname, OLD.inuse) IS DISTINCT FROM
     (NEW.units, NEW.unitname, NEW.inuse)
EXECUTE PROCEDURE log_unitsref();

I quote the manual on CREATE TRIGGER:

UPDATE OF ...

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

WHEN ...

A Boolean expression that determines whether the trigger function will actually be executed.

Note that these two elements are closely related but neither mutually exclusive nor redundant.

  • It is much cheaper not to fire the trigger at all, if no column of interest is involved.

  • It is much cheaper not to execute the trigger function if no column of interest was actually altered.

Related answers here or here ...

Upvotes: 4

Related Questions