Triggers: The beauty of abstraction

It is impressive to see how little abstraction and what kind of lousy designs some
commercial database vendors tend to offer. This is most obvious when writing
something trivial like a trigger.

Actually most people using PostgreSQL don't think about triggers too much – you
simply write on, test it and you are done. I don't know of too many people who
ever give it a deeper thought.

Especially during trainings, however, reality comes crashing down on me.
Students tell me about their experiences with their non-PostgreSQL database system and what
they say does not sound as easy as PostgreSQL. The key thing is that commercial
systems usually force the user to execute the code which is directly defined
inside the trigger. In PostgreSQL a trigger always calls a function – this has
the main advantage that there is always a level of abstraction around and the
very same code can be reused for many different triggers to work on many
different tables. In addition to that you can always have as many triggers as
you want on the very same table and PostgreSQL will make sure that those triggers are executed in
alphabetic order.

This provides PostgreSQL users with an edge over, say, MaxDB or some other
commercial database. In MaxDB and many others you can only have one trigger per
action per table (so, one for INSERT, one for UPDATE and one for DELETE).
This is especially nasty if you want to change things later on.