Insert Trigger Behavior Change?

Question

Insert Trigger Behavior Change?

Art Sennholtz

Old Hand

Points: 355
More actions
October 8, 2007 at 8:36 am

#182627

We have a table where there are multiple no dupe conditions so we wrote a trigger that would check for these conditions and, if they existed, would rollback the transaction.
This worked okay on SQL2000 but when testing this trigger on SQL2005 it always detects a duplicate. It seems to be finding the row that is being inserted that caused the trigger to fire.
Does anyone have any information about this behavior change?
Thanks!
Art

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

can you post the trigger you are using for SQL2005? Also, could it be that you changed the trigger to After insert or something on 2005, when it was INSTEAD of insert on 2000?

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!

Art Sennholtz Old Hand Points: 355 More actions · Answer 2

Thanks for the reply!

Here's the script of the trigger. The trigger was not changed at all. I just restored the database from a SQL2000 installation.

CREATE TRIGGER [dbo].[trg_STN_ParserCodes_Unique]

On [dbo].[STN_ParserCodes]

For Insert, Update

As

Begin

Declare

@STN_Code char(1),

@data varchar(200),

@Code varchar(200),

@Error varchar(500)

Set @Error = ''

Select @STN_Code = STN_Code, @data = Data, @Code = Code

From Inserted

If @STN_Code = 'P'

Begin

If Exists(Select PCKey From STN_ParserCodes Where STN_Code = 'P' And Code = @Code)

Set @Error = 'A record for with STN_Code: "' + @STN_Code + '" and Code value of "' + @Code + '" already exists, Transaction Failed'

End

Else

Begin

If Exists(Select PCKey From STN_ParserCodes Where STN_Code = @STN_Code And Data = @data)

Set @Error = 'A record for with STN_Code: "' + @STN_Code + '" and Data value of "' + @data + '" already exists, Transaction Failed'

End

If Len(@Error) > 0

Begin

Rollback Transaction

Raiserror(@Error, 11, 1)

End