|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 08, 2009 2:25 AM
Points: 4,
Visits: 25
|
|
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
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 525,
Visits: 10,303
|
|
| 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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 08, 2009 2:25 AM
Points: 4,
Visits: 25
|
|
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"
|
|
|
|
