After Trigger not able to update multiple records with unique constraints

Question

Post reply

After Trigger not able to update multiple records with unique constraints

Minaz Amin

SSChampion

Points: 11052
More actions
January 15, 2015 at 5:09 am

#313751

Hi all,
I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
Could like to know the reason.
"More Green More Oxygen !! Plant a tree today"

Viewing 11 posts - 1 through 10 (of 10 total)

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

At least share an error message. Is it "Subquery returned more than 1 value" or what ? Or better show the trigger .

Minaz Amin SSChampion Points: 11052 More actions · Answer 2

actually I don't receive any error. This a replicated update data from publisher to subscriber. Trigger is defined on subscriber.

If I update on the subscriber I am able to see the data but if I update records on my publisher the update with multiple records involving unique constraints does not propagate to subscriber. But I am able to replicate single records.

Please do advice.

"More Green More Oxygen !! Plant a tree today"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Please post the trigger code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Minaz Amin SSChampion Points: 11052 More actions · Answer 4

My update from publisher will update the MONO.USER_MASTER table as well as the same table on subscriber. On Subscriber , we have defined the trigger to update FLI.USER_MASTER table.

Also I think we are suffering from Bounded Update as explained in the link below. If you could

guide me more on this that will be helpful.

http://blogs.msdn.com/b/repltalk/archive/2011/10/04/sql-server-transaction-replication-bounded-update-or-why-my-update-was-transformed-into-an-delete-insert-pair.aspx

Code:

USE [Database]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [InsOrUpd_Trg_USER_MASTER]

ON [USER_MASTER]

AFTER INSERT, UPDATE

AS

BEGIN

SET NOCOUNT ON

DECLARE @USER_MASTER_ID INT

DECLARE @OPERATION CHAR(1) SET @OPERATION= 'N'

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) SET @OPERATION = 'U'

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) SET @OPERATION = 'I'

IF @OPERATION = 'I'

BEGIN

SET IDENTITY_INSERT FLI.USER_MASTER ON

INSERT INTO FLI.USER_MASTER

( FIRSTNAME,

LASTNAME,

RELATIONSHIP_MASTER_ID,

PRIMARY_EMAIL_ID,

[USER_NAME], -- unique constraint

CONTACT_CODE, -- unique constraint

DELETED_BY,

DELETED_DT

)

SELECT FIRSTNAME,

LASTNAME,

RELATIONSHIP_MASTER_ID,

PRIMARY_EMAIL_ID,

[USER_NAME],

USER_ACTIVATED_FLAG,

LDAP_ENTRY_REQUIRED_FLAG,

CONTACT_CODE,

DELETED_BY,

DELETED_DT

FROM INSERTED;

SET IDENTITY_INSERT FLI.USER_MASTER OFF

END

IF @OPERATION = 'U'

BEGIN

UPDATE UM SET

UM.FIRSTNAME = U.FIRSTNAME,

UM.LASTNAME = U.LASTNAME,

UM.RELATIONSHIP_MASTER_ID = U.RELATIONSHIP_MASTER_ID,

UM.PRIMARY_EMAIL_ID = U.PRIMARY_EMAIL_ID,

UM.[USER_NAME] = U.[USER_NAME],

UM.CONTACT_CODE = U.CONTACT_CODE,

UM.DELETED_BY=U.DELETED_BY,

UM.DELETED_DT=U.DELETED_DT

FROM FLI.USER_MASTER UM, INSERTED U

WHERE UM.USER_MASTER_ID =U.USER_MASTER_ID

END

"More Green More Oxygen !! Plant a tree today"

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Review books online and online sources for NOT FOR REPLICATION.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

I tried that , but we need the trigger to update FLI table hence I cannot use NOT FOR REPLICATION.

"More Green More Oxygen !! Plant a tree today"

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

No, my point was to make sure it wasn't set ON by accident. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 8

In the posted trigger code there is DECLARE @USER_MASTER_ID INT which is never used. I guess code is simplified for a reason. Any chance that some @USER_MASTER_ID calculations are causing the problem?

Minaz Amin SSChampion Points: 11052 More actions · Answer 9

I did find the issue and resolved it.:-)

Issue : Multiple rows were not able to get applied on subscription when AFTER INSERT ,UPDATE trigger is enabled.

Data flow: Publisher - Table --> Subscriber table --> After trigger --> FLI Table

Solution: Update statement were considered as delete / insert pair. When we update multiple rows with unique key constraint update fails due to trigger enforce constraint violation and abort the transactions. :w00t:

Added AFTER insert, update ,delete -- delete is add in the definition and added a code designed to handle delete operation.

After this it is able to replicate and update the subscriber as well as FLI table. 🙂

"More Green More Oxygen !! Plant a tree today"

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 10

serg-52

SSCrazy Eights

Points: 9913

January 16, 2015 at 4:20 am

#1770689

Thanks for sharing it.