SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Sunday, June 25, 2006

trigger/(exists) problem

SQL Apprentice Question
I created an insert trigger on a table that uses two columns as a key.
The trigger checks for an existing record with the same values for
those two columns and throws a user friendly error message. The
problem comes when I determine if a record with those values already
exist (e.g. -
if exists (Select * from a, inserted b where a. =
b. and a. = b.)
begin
'some code here
end
)

I truncated and the 'some code here still executes. I even
tried "select * from " after a truncate and the 'some code
here still executes..


Is there something I'm not doing correctly in my ALTER TRIGGER
statement? I've also noticed there is some type of unexplained delay
in that I can drop the trigger and inserts still throw the old error
message in query analyzer. After I can recreate the trigger and the
standard primary key violation error will be thrown.


oh..btw I'm using SQL2K



Celko Answers
>> I created an insert trigger on a table that uses two columns as a key. The trigger checks for an existing record [sic] with the same values for those two columns and throws a user friendly error message. <<


Why did you think about procedural trigger code instead of a
declarative constraint? Let the front end deal with the user input and
the back end deal with the data. This is the whole idea of a tiered
architecture. For example, all your TRIM() operations ought to be
inside a CHECK() constraint to prevent bad data in the table, not a
after-thought in a trigger or block of procedural code.

No comments: