Feeds

First among SQLs

COBOL for lawyers

  • alert
  • submit to reddit

Beginner's guide to SSL certificates

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. ®

Providing a secure and efficient Helpdesk

More from The Register

next story
Microsoft on the Threshold of a new name for Windows next week
Rebranded OS reportedly set to be flung open by Redmond
Business is back, baby! Hasta la VISTA, Win 8... Oh, yeah, Windows 9
Forget touchscreen millennials, Microsoft goes for mouse crowd
SMASH the Bash bug! Apple and Red Hat scramble for patch batches
'Applying multiple security updates is extremely difficult'
Apple: SO sorry for the iOS 8.0.1 UPDATE BUNGLE HORROR
Apple kills 'upgrade'. Hey, Microsoft. You sure you want to be like these guys?
ARM gives Internet of Things a piece of its mind – the Cortex-M7
32-bit core packs some DSP for VIP IoT CPU LOL
Lotus Notes inventor Ozzie invents app to talk to people on your phone
Imagine that. Startup floats with voice collab app for Win iPhone
'Google is NOT the gatekeeper to the web, as some claim'
Plus: 'Pretty sure iOS 8.0.2 will just turn the iPhone into a fax machine'
prev story

Whitepapers

A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
Storage capacity and performance optimization at Mizuno USA
Mizuno USA turn to Tegile storage technology to solve both their SAN and backup issues.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Beginner's guide to SSL certificates
De-mystify the technology involved and give you the information you need to make the best decision when considering your online security options.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.