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. — , Aug 22 '13 at 13:50
@Hyposaurus check my answer. Is it what you want or do you need more help? — Roman Pekar, Aug 24 '13 at 16:13

Roman Pekar · Accepted Answer · 2013-08-22T14:03:16.817

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 '13 at 14:03

answered Aug 22 '13 at 13:50

Roman Pekar

107,110
28
195
197

Great this is the thing! Thanks for the help ((I went on holidays so that's the delay there. – Doug Miller Sep 10 '13 at 08:06

postgres function that should update date not executing

1 Answers1