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
'some code here
tried "select * from
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
>> 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.