Create auto update trigger for single field update postgres

Question

I have a table named vehicle like this

CREATE TABLE vehicle
(
    id                  varchar,
    status              TEXT,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (id)
   
);

I want to create a trigger which will on trigger only when the status value is modified.

till now have a trigger which work on whole table fields and it is written like this

first function

CREATE OR REPLACE FUNCTION public.update_timestamp()
    RETURNS trigger
    LANGUAGE plpgsql
AS
$function$
BEGIN
    NEW.status_updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$function$
;

then trigger

create trigger update_timestamp
    before update
    on vehicle
    for each row
execute procedure update_timestamp();

reference for this trigger is this https://x-team.com/blog/automatic-timestamps-with-postgresql/

@LaurenzAlbe both cases . status change in any combination

newbir
– newbir

2021-05-26 07:51:37 +00:00
Commented May 26, 2021 at 7:51 — newbir
– newbir, Commented May 26, 2021 at 7:51

eshirvana · Accepted Answer · 2021-05-26 01:43:30Z

2

you can change your trigger to execute trigger function only when status has changed:

create trigger update_timestamp
    before update
    on vehicle
    for each row
    when (OLD.status IS DISTINCT FROM NEW.status)
execute procedure update_timestamp();

answered May 26, 2021 at 1:43

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create auto update trigger for single field update postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related