Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table:

CREATE TRIGGER touch_users
BEFORE UPDATE
ON users
FOR EACH ROW
WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
EXECUTE PROCEDURE touch_modification_time();
CREATE TRIGGER touch_company
BEFORE UPDATE
ON company
FOR EACH ROW
WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
EXECUTE PROCEDURE touch_modification_time();
-- etc for each table

This gets tedious fairly quickly; especially since they're exactly the same, except for the table name.

Is there a way I can reduce the boilerplate required for each table? Or possibly even eliminate it altogether with a single trigger that acts on every table by default?

Write a small bit of perl (or whatever you fancy) that writes the code for you.
–
tblPhilMar 28 '14 at 23:16

And why row triggers? Statement level triggers seem to be good for this simple task. And execute a whole procedure? Why? Don't you just want to update the modification_time with the current datetime?
–
ypercubeMar 28 '14 at 23:18

@ypercube - the particular logic in this trigger is to only update modification_time if it omitted; e.g. allow it to be explicitly updated if desired. I'm not an sql guru; I'm not familiar with statement triggers.
–
pimlottcMar 28 '14 at 23:31

@ypercube How would a statement trigger be useful here? It's inherently per-row, and has to alter the NEW tuple in the BEFORE phase.
–
Craig RingerMar 29 '14 at 1:31