First among SQLs

Trigger is a horse's name

I guess it doesn't matter, because no two implementations of SQL achieve sufficient compatibility with each other that one could run unchanged the simplest demo in the Barry Bear's Big Book of Baby Databases for Tiny Tots (ed J Celko, looking to tone down the elitist stance established by SQL for Smarties). If you want a laugh - albeit a somewhat modest and bitter laugh - just look up the syntax for triggers on Wikipedia. (Here you are.) Depending on which exact flavour of SQL you are driving, the choices will look something like this:

CREATE TRIGGER <TRIGGERNAME>
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
ON {<TABLENAME>|<VIEWNAME>}
[REFERENCING NEW {ROW | STATEMENT} AS N, OLD {ROW | STATEMENT} AS O]
FOR EACH {ROW | STATEMENT}
[WHEN <BOOLEAN EXPRESSION>]

and we haven't even got to the doings of the trigger yet - this is all preamble. (However it does show something of SQL's preposition obsession. Somebody said once said that there is no problem in programming that cannot be overcome by adding levels of indirection. SQL, I believe, sees it more as a useful evasion tactic, with BEFORE, AFTER, INSTEAD OF, FROM, BY, INTO, AS and their ilk are introduced at every turn as a way of putting off doing something.)

Not that you should be considering using a trigger in a database design. Triggers spring from the automatic constraint-enforcement view of database design, and the lamentable notion that business rules can sensibly be enforced by the RDBMS. Besides, they encourage DBAs, who (as any right-thinking programmer will tell you) have way too much power anyway.

One danger, witnessed by the aforementioned colleague while working with a government database, is that a single action can set off a cascade of triggers. On one occasion, after he had incautiously deleted a row from a certain table, he noticed a rumbling noise, as if of an avalanche, caused by all the disk drives in the building turned on simultaneously, and the lights dimmed alarmingly as cascades of data disappeared into the transaction log's greedy maw.

Another problem can best be demonstrated by a thought experiment. Imagine designing a perfect romance database, in which every doctor must get her nurse, and every nurse his doctor. These constraints are implemented by triggers. Now, whenever you attempt to insert a row into either the nurse or doctor table, you will be blocked by one of the constraints. The whole database has seized as solid as the gears on a £1 coin.

'Aha!', I hear you cry, 'but what if you inserted doctor/nurse pairs as single transactions?'

To which I reply: you may know your SQL, but you know nothing of Lurve. ®

• Stob on The Reg
• Verity Stob book