CODEWITHSUNDEEP
postgresql
imlearningcode
imlearningcode

Reputation: 411

postgresql how stop trigger adding a row if nothing has changed

I would like to record changes to my working table into my history table but only if a value has changed during an UPDATE, I tried creating an update_history trigger but it adds a row regardless of whether a value has been changed or not, for example say I have this in my working table:

shift_id|site     |organisational_unit
--------|---------|-------------------
  123475|site01   |my org

If I perform an update query

UPDATE working SET site = $1, organisational_unit = $2 WHERE shift_id=$3', ['site01', 'my new org', '123475']

This creates a row in the history table one for site even thought it hasn't changed value I only want a new row for the organisational_unit change

historyid|shiftid|fieldname          |oldvalue |newvalue   |updatedat          |
---------|-------|-------------------|---------|-----------|-------------------|
        7| 123475|organisational_unit|my org   |my new org |2019-07-01 10:21:19|
        8| 123475|site               |site01   |site01     |2019-07-01 10:21:19|

My trigger looks like this 

-- create function for updates to track history
CREATE function update_history ()
RETURNS TRIGGER
as $$
BEGIN
    -- check if data in column has changed between the update
    IF NEW.client_id <> OLD.client_id THEN

        -- if it has insert a row to the history table detailing the changes
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(New.shift_id, 'client id ', OLD.client_id, NEW.client_id);

    -- if nothing has changed don't do anything
    END IF;

    IF NEW.organisational_unit <> OLD.organisational_unit THEN
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(New.shift_id, 'organisational_unit', OLD.organisational_unit, NEW.organisational_unit);
    END IF;

    IF NEW.site <> OLD.site THEN
    INSERT INTO history
        (ShiftId, fieldName, OldValue, NewValue)
    VALUES(New.shift_id, 'site', OLD.site, NEW.site);
    END IF;

return null;
END;
$$
language plpgsql;

Upvotes: 6

Views: 2025

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

To check whether a column has changed or not, do not use <>. It doesn't take nulls into account. use IS DISTINCT FROM

IF NEW.client_id IS DISTINCT FROM OLD.client_id
...

Or check the whole row using IF NEW IS DISTINCT FROM OLD

If you want to prevent the insert into main table from happening, it should be a BEFORE UPDATE trigger and you should do a

return null;

only where you don't want an INSERT

Upvotes: 6

Laurenz Albe
Laurenz Albe

Reputation: 246268

The most efficient way is to define a trigger that only fires when some column changes:

CREATE TRIGGER ... BEFORE UPDATE ON ... FOR EACH ROW
   WHEN (NEW IS DISTINCT FROM OLD)
   EXECUTE FUNCTION update_history();

That avoids executing the function in unnecessary cases.

Upvotes: 16

Related Questions