Re: How to specify that a trigger should fire when column is NOT in SET-clause?

On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:
> Hi.
> I need to set a value in a trigger if a column is explicitly NOT 
> specified in UPDATE's SET-clause.
> Like for example having a "BEFORE UPDATE OF NOT"
> 
> create TRIGGER my_trigger
>      BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN (OLD.val <>NEW.val)
> EXECUTE PROCEDURE do_stuff();
> 
> I want the trigger to be fired when the column "modified" is NOT 
> specified, is it possible?

It will always be specified, it may or may not be changed. As example:

                       Table "public.animals"
  Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
  id     | integer                |           | not null |
  cond   | character varying(200) |           | not null |
  animal | character varying(200) |           | not null |

CREATE OR REPLACE FUNCTION public.trg_test()
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
     RAISE NOTICE 'New id is: %, Old id is: %', NEW.id, OLD.id;
     RAISE NOTICE 'New cond is: %, Old cond is: %', NEW.cond, OLD.cond;
     RAISE NOTICE 'New animal is: %,  Old animal is: %', NEW.animal, 
OLD.animal;
     RETURN NEW;
END;
$function$
;

update animals set cond = 'skinny' where id = 4;
NOTICE:  New id is: 4, Old id is: 4
NOTICE:  New cond is: skinny, Old cond is: slim
NOTICE:  New animal is: dog,  Old animal is: dog
UPDATE 1



> Or - is it possible to check for this in the trigger-function?

As David Johnson mentioned you can check whether the value for the 
column is changed:

NEW.animal <> OLD.animal



> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com