Within a Trigger Function, How to Get Which Fields Are Being Updated

Within a trigger function, how to get which fields are being updated

If a "source" doesn't "send an identifier", the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly.

If the "source" is identifiable by any session information function, you can work with that. Like:

NEW.column = session_user;

Unconditionally for every update.

General Solution

I found a way how to solve the original problem. The column will be set to a default value in any update where the column is not updated (not in the SET list of the UPDATE).

Key element is a per-column trigger introduced in PostgreSQL 9.0 - a column-specific trigger using the UPDATE OF column_name clause.

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

That's the only simple way I found to distinguish whether a column was updated with a new value identical to the old, versus not updated at all.

One could also parse the text returned by current_query(). But that seems tricky and unreliable.

Trigger functions

I assume a column source defined NOT NULL.

Step 1: Set source to NULL if unchanged:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source = OLD.source THEN
      NEW.source := NULL;      -- "impossible" value (source is NOT NULL)
   END IF;

   RETURN NEW;
END
$func$;

Step 2: Revert to old value. Trigger will only be fired, if the value was actually updated (see below):

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := OLD.source;
   END IF;

   RETURN NEW;
END
$func$;

Step 3: Now we can identify the lacking update and set a default value instead:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := 'UPDATE default source';  -- optionally same as column default
   END IF;

   RETURN NEW;
END
$func$;

Triggers

The trigger for Step 2 is fired per column!

CREATE TRIGGER upbef_step1
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE TRIGGER upbef_step2
  BEFORE UPDATE OF source ON tbl             -- key element!
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step2();
    
CREATE TRIGGER upbef_step3
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step3();

db<>fiddle here

Trigger names are relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

The procedure could be simplified with something like "per-not-column triggers" or any other way to check the target-list of an UPDATE in a trigger. But I see no handle for this, currently (unchanged as of Postgres 14).

If source can be NULL, use any other "impossible" intermediate value and check for NULL additionally in trigger function 1:

IF OLD.source IS NOT DISTINCT FROM NEW.source THEN
    NEW.source := '#impossible_value#';
END IF;

Adapt the rest accordingly.

How to determine what fields were update in an update trigger

you can use one of my favorite XML-tricks to do this:

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)

end;

sql fiddle demo

Best way to detect which columns was updated when using triggers

There's a function you can call within a trigger called COLUMS_UPDATED(). This returns a bitmap of the columns in the trigger table, with a 1 indicating the corresponding column in the table was modified. On it's own that's not brilliant, as you'd end up with a list of column indices, and would still need to write a CASE statement to determing which columns were modified. If you've not got many columns that might not be so bad, but if you wanted something more generic you could perhaps join to information_schema.columns to translate those columns indices into column names

Here's a little sample to show the sort of thing you could do:

CREATE TABLE test (
ID Int NOT null IDENTITY(1,1),
NAME VARCHAR(50),
Age INT
)

 GO
---------------------------------------
CREATE TRIGGER tr_test ON test FOR UPDATE 

AS

DECLARE @cols varbinary= COLUMNS_UPDATED()
DECLARE @altered TABLE(colid int)
DECLARE @index int=1

WHILE @cols>0 BEGIN
    IF @cols & 1 <> 0 INSERT @altered(colid) VALUES(@index)
    SET @index=@index+1
    SET @cols=@cols/2
END 

DECLARE @text varchar(MAX)=''
SELECT @text=@text+C.COLUMN_NAME+', '
FROM information_schema.COLUMNS C JOIN @altered A ON c.ordinal_position=A.colid
WHERE table_name='test'

PRINT @text

GO
------------------------------------------------------
INSERT test(NAME, age) VALUES('test',12)
UPDATE test SET age=15
UPDATE test SET NAME='fred'
UPDATE test SET age=18,NAME='bert'
DROP TABLE test

Update field updated by trigger

At least four options:

• Set to in sync as another user and test current_user in the trigger;

• Define a custom config variable (GUC) and SET LOCAL or set_config(...) it in the transaction before updating the in sync field; test that GUC in the trigger and change behaviour based on it;

• Temporarily disable the trigger in the transaction before setting in sync;

• Have the trigger check if all the other values are unchanged by the update and allow in sync to be set to true if no other values have changed. Use IS DISTINCT FROM for this test to handle nulls conveniently.

I'd probably use a custom GUC myself, with current_setting('my.guc') to fetch the value from within the trigger.

If you're on Pg 9.1 or older you must add my (or whatever you really call the prefix) to custom_variable_classes. In 9.2 and above any variable with a period (.) in it is assumed to be a custom variable.

See also passing user ID to triggers.

Trigger using adjusted OLD / NEW values

There is much amiss here:

• trigger functions must return a row, typically (a modified) NEW, or NULL to abort the operation

• you do not update NEW since it is not a table, but you assign to its columns, like

  NEW.created := current_timestamp;
  

• for BEFORE DELETE triggers NEW is NULL, since there is no new version of the row

Detecting column changes in a postgres update trigger

Read up on the hstore extension. In particular you can create a hstore from a row, which means you can do something like:

changes := hstore(NEW) - hstore(OLD);
...pg_notify(... changes::text ...)

That's slightly more information than you wanted (includes new values). You can use akeys(changed) if you just want the keys.

PostgreSQL trigger if one of the columns have changed

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.

---

Related Topics