CODEWITHSUNDEEP
sqlpostgresqltimestamp
newbir
newbir

Reputation: 23

Create auto update trigger for single field update postgres

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/

Upvotes: 1

Views: 1552

Answers (1)

eshirvana
eshirvana

Reputation: 24568

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();

Upvotes: 2

Related Questions