triggers in multi client update

Question

triggers in multi client update

aviad shalom-437256

SSC Rookie

Points: 34
More actions
November 16, 2008 at 7:57 am

#75363

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

if the table1.Key field should not repeat, why not put a unique constraint on it? why are you trying to do a constraint in a trigger, when there's a built in ability to do that?

I'm missing something here i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I agree with Lowell, sounds like a Unique Contraint to me.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question