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@xxxxxxxxxx <mailto:andreas@xxxxxxxxxx>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
