Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Sunday, October 21, 2007

Sql Server - The Undefined Trigger

Ask the question: does Sql Server have a row level trigger.
Answer: it depends. From the point of view of application
development how in the world can the answer possibly be
ambiguous? Well lets back up and look at the Update statement.
From Sql Server 2005 bol:
Using UPDATE with the FROM Clause
'The results of an UPDATE statement are undefined if the
 statement includes a FROM clause that is not specified in
 such a way that only one value is available for each column
 occurrence that is updated, that is if the UPDATE statement
 is not deterministic'.
Ok what is really the rational for even keeping this
'proprietary' syntax? Since it is inherently non-deterministic
why even offer it to developers most of whom do not understand
but the simplest of sql. After all Sql Server is explicitly
relinquishing responsibility for the integrity of the Update and
making the user responsible. Shouldn't the idea of a database be
the other way around? Ok now lets move to the trigger. From bol
under the topic: Multirow Considerations for DML Triggers:
'When you write the code for a DML trigger, consider that the
 statement that causes the trigger to fire can be a single statement
 that affects multiple rows of data, instead of a single row.'
Does this sound familiar? Of course it does. The same non-determinism
of the FROM clause in Update now reappears in the insert table
of the trigger. But of course it is not presented as a 'trigger
is inherently undefined'. No, now the undefined nature of the
trigger is called 'a Multirow consideration'. And again the server
reliquishes responsibility of integrity and puts it in the
hands of the user. Well lets get real. There is no such thing as
a multirow trigger. From an applications standpoint the largest
scope of a trigger is a row. The so called multirow trigger is an 
example of what happens when users don't scrutinize what nonsense
is thrown their way. Instead of getting rid of a stupid idea
in Update using a FROM it was instead extended to a trigger.
It was simply an easy and expedient thing to do. But it was
shameful and lazy too. Lack of integrity is based on lack of
character. But it is equally disappointing that more users do
not complain and demand the integrity that a database should offer.
Sql Server can do much, much better.  But, like E.T., users must
phone home.

1 comment:

muebles collado villalba said...

I saw a great deal of helpful information above!

About Me

My photo
Phoenix, Arizona, United States