CODEWITHSUNDEEP
sqlpostgresqltriggers
nowszy94
nowszy94

Reputation: 3331

Trigger for update timestamp field

I'm trying to create trigger, that updates my lastmodified field on insert/update. Here's my trigger:

CREATE OR REPLACE TRIGGER timestamp_trigger BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE PROCEDURE timestamp_update;

and my procedure:

CREATE OR REPLACE FUNCTION timestamp_update()
RETURNS TRIGGER 
AS
$$
BEGIN
    UPDATE orders
    SET lastmodified = getdate()
    WHERE orderid = new.orderid
    RETURN new; 
END;
$$ language plpgsql;

Unfortunately when I try to update some field, field isn't updated. Also when i create row, the lastmodified field isn't updated.

Upvotes: 0

Views: 1061

Answers (1)

klin
klin

Reputation: 121534

Do not use update in a trigger like this because it may lead to endless recursive loop. If you want to modify inserted/updated row, just change the new record:

CREATE OR REPLACE FUNCTION timestamp_update()
RETURNS TRIGGER 
AS
$$
BEGIN
    NEW.lastmodified = now();
    RETURN new; 
END;
$$ language plpgsql;

The syntax create or replace trigger is incorrect.

CREATE TRIGGER timestamp_trigger 
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE PROCEDURE timestamp_update();

Upvotes: 2

Related Questions