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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com