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

Beginner's guide to SSL certificates

More from The Register

next story
Google+ goes TITSUP. But WHO knew? How long? Anyone ... Hello ...
Wobbly Gmail, Contacts, Calendar on the other hand ...
Preview redux: Microsoft ships new Windows 10 build with 7,000 changes
Latest bleeding-edge bits borrow Action Center from Windows Phone
UNIX greybeards threaten Debian fork over systemd plan
'Veteran Unix Admins' fear desktop emphasis is betraying open source
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
Google opens Inbox – email for people too stupid to use email
Print this article out and give it to someone techy if you get stuck
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Redmond top man Satya Nadella: 'Microsoft LOVES Linux'
Open-source 'love' fairly runneth over at cloud event
prev story

Whitepapers

Cloud and hybrid-cloud data protection for VMware
Learn how quick and easy it is to configure backups and perform restores for VMware environments.
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.
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?
Three 1TB solid state scorchers up for grabs
Big SSDs can be expensive but think big and think free because you could be the lucky winner of one of three 1TB Samsung SSD 840 EVO drives that we’re giving away worth over £300 apiece.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.