November 16, 2008 at 8:00 am
hello.
I have a client/server application that use sql server 2005.
I have some problem with my trigger that does data integrity check
the trigger works fine most of the time
in a special case when 2 clients save data that cause violation at the exact same time (with different connection and thred) both records are save and I get data violation in my DB. an operation that normally my triggers know how to deal with
the trigger:
Create trigger TR1 on table1 for insert,update
AS
IF EXISTS(select 1 from inserted i, Table1 T where i.ID <> T.ID and
i.Key = T.Key)
BEGIN
RAISERROR ('The given key is already exists',16,1)
rollback transaction
return;
end
end
this trigger should not allow the same key in the table but allow NULL value
November 16, 2008 at 8:09 am
why dont you use a UNIQUE contraint rather than using this trigger for data integrity? Then get your app to check that the insert doesnt fail, if it does, return an error to the application saying it already exists. The advantage of this over a primary key is that you can use a null value in the column, whereas a primary key you cant.
November 16, 2008 at 8:23 am
thanks for the quick reply
i can not use the constraint because the key field can be null and i have a bool field that indicate that this row is deleted i forgot to copy this check to the trigger so technicaly i have the same key tin the table but with record that "deleted"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply