From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Andreas Joseph Krogh <andreas(at)visena(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |
Date: | 2020-12-25 16:48:39 |
Message-ID: | dacbcd40-3cef-074b-71a6-f1806884f62b@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-12-25 16:57:21 | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |
Previous Message | David G. Johnston | 2020-12-25 16:04:16 | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |