Feeds

First among SQLs

COBOL for lawyers

  • alert
  • submit to reddit

Security for virtualized datacentres

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

Secure remote control for conventional and virtual desktops

More from The Register

next story
New 'Cosmos' browser surfs the net by TXT alone
No data plan? No WiFi? No worries ... except sluggish download speed
'Windows 9' LEAK: Microsoft's playing catchup with Linux
Multiple desktops and live tiles in restored Start button star in new vids
iOS 8 release: WebGL now runs everywhere. Hurrah for 3D graphics!
HTML 5's pretty neat ... when your browser supports it
Mathematica hits the Web
Wolfram embraces the cloud, promies private cloud cut of its number-cruncher
Mozilla shutters Labs, tells nobody it's been dead for five months
Staffer's blog reveals all as projects languish on GitHub
'People have forgotten just how late the first iPhone arrived ...'
Plus: 'Google's IDEALISM is an injudicious justification for inappropriate biz practices'
SUSE Linux owner Attachmate gobbled by Micro Focus for $2.3bn
Merger will lead to mainframe and COBOL powerhouse
iOS 8 Healthkit gets a bug SO Apple KILLS it. That's real healthcare!
Not fit for purpose on day of launch, says Cupertino
prev story

Whitepapers

Secure remote control for conventional and virtual desktops
Balancing user privacy and privileged access, in accordance with compliance frameworks and legislation. Evaluating any potential remote control choice.
WIN a very cool portable ZX Spectrum
Win a one-off portable Spectrum built by legendary hardware hacker Ben Heck
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?
The next step in data security
With recent increased privacy concerns and computers becoming more powerful, the chance of hackers being able to crack smaller-sized RSA keys increases.