postgres function that should update date not executing

Question

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?

Please include the relevant part of your code in your question and don't link to an external site. — user1907906
– user1907906, Commented Aug 22, 2013 at 13:50
@Hyposaurus check my answer. Is it what you want or do you need more help? — roman
– roman, Commented Aug 24, 2013 at 16:13

roman · Accepted Answer · 2013-08-22 14:03:16Z

1

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

edited Aug 22, 2013 at 14:03

answered Aug 22, 2013 at 13:50

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

Doug Miller Over a year ago

Great this is the thing! Thanks for the help ((I went on holidays so that's the delay there.

Collectives™ on Stack Overflow

postgres function that should update date not executing

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related