Take the 2-minute tour ×
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?

share|improve this question
1  
Write a small bit of perl (or whatever you fancy) that writes the code for you. –  Phil Mar 28 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? –  ypercube Mar 28 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. –  pimlottc Mar 28 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 Ringer Mar 29 at 1:31
    
@CraigRinger: Aren't statement triggers more efficient than row triggers? –  ypercube Mar 29 at 4:59

1 Answer 1

Single trigger function for multiple triggers: possible - that's what you do.
Single trigger for multiple tables: not possible.

But you can shorten the code for creating lots of triggers:

DO
$$
BEGIN

EXECUTE (
SELECT string_agg('CREATE TRIGGER touch_users
   BEFORE UPDATE ON ' || quote_ident(t) || '
   FOR EACH ROW
   WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
   EXECUTE PROCEDURE touch_modification_time();'
 , E'\n')
FROM unnest('{users, company, foo, bar}'::text[]) t -- list your tables here
);

END
$$;

Or gather table names from system catalogs:
Tables accessed during last periods in postgresql

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.