CODEWITHSUNDEEP
sqlpostgresqltriggers
Doug Miller
Doug Miller

Reputation: 1346

postgres function that should update date not executing

I have the following schema setup in PG 8.4 SQLFiddle

The idea is to write a generic function so that when ever the article relation is updated or the workshop relation is updated then the modified date is set to the current date.

I have this building fine but the functoion does not appear to actually update the date when ut should. This question looked interesting but it was not a generaly purpose function: Accepted answer.

What should I be doing to get thsi working?

Upvotes: 1

Views: 119

Answers (1)

roman
roman

Reputation: 117636

You've used AFTER triggers for update value in NEW row. Try BEFORE triggers:

-- Articles table
CREATE TRIGGER update_articles_modified_date_to_now BEFORE UPDATE
ON articles FOR EACH ROW EXECUTE PROCEDURE 
  update_modified_date_to_now();

-- Workshop table
CREATE TRIGGER update_workshop_modified_date_to_now BEFORE UPDATE
ON workshop FOR EACH ROW EXECUTE PROCEDURE 
  update_modified_date_to_now();

sql fiddle demo

Upvotes: 1

Related Questions