How to prevent recursion of update trigger on the same table [Resolved]

Posted by Bhanubysani under Sql Server on 6/30/2015 | Points: 10 | Views : 464 | Status : [Member] | Replies : 2
I have a requirement where the table contains the values like below.

UniqueId custid mobileno emailid profileId
1 101 9440765465 xxx@gmail.com 120
2 101 9440766666 xxx@gmail.com 121
3 102 9555222222 xxx@gmail.com 122

User has an ability to update the records from a screen by selecting one record, but i have a condition as user wants to update any record of particular customerid we need to check the record as that customerid has multiple records exist in the table and update all the records in single shot.

From UI screen he has not able to do this one. For this i have written a update trigger on that table in that i am writing an update statement to update all the records which have the same customer id.

Its working fine as per my expectation.

My concern is suppose a user update a record whose customerid is 101 from UI, i am updating all the records whose customerid is 101 in table. It will cause any recursion on table.

[code]
create TRIGGER CustomerUpdateTrigger ON NOMINATEDUSER
FOR UPDATE
AS
declare @IDCHANNELUSER varchar(100);
declare @NOMINATEDUSERID varchar(100);
declare @NOMINATEDUSERNAME varchar(100);
declare @PHONENUMBER varchar(100);
declare @UPDATEDBY varchar(100);
declare @CUSTID varchar(100);
declare @LASTUPDATED datetime;


select @IDCHANNELUSER=i.IDCHANNELUSER from inserted i;
select @NOMINATEDUSERID=i.NOMINATEDUSERID from inserted i;
select @NOMINATEDUSERNAME=i.NOMINATEDUSERNAME from inserted i;
select @PHONENUMBER=i.PHONENUMBER from inserted i;
select @UPDATEDBY=i.UPDATEDBY from inserted i;
select @LASTUPDATED=i.LASTUPDATED from inserted i;
select @CUSTID=i.CUSTID from inserted i;

update NOMINATEDUSER set IDCHANNELUSER=@IDCHANNELUSER,NOMINATEDUSERID=@NOMINATEDUSERID,NOMINATEDUSERNAME=@NOMINATEDUSERNAME,
PHONENUMBER=@PHONENUMBER,LASTUPDATED=@LASTUPDATED,UPDATEDBY=@UPDATEDBY WHERE CUSTID= @CUSTID

[/code]


GO

Please help me how to prevent recursion.

Regards,
Bhanu Prakash Bysani



Responses

Posted by: Sheonarayan on: 6/30/2015 [Administrator] HonoraryPlatinum | Points: 50

Up
0
Down

Resolved
The ideal solution of this problem is to have a unique Auto increment id, see this http://techfunda.com/Howto/sql-server/16/auto-increment-column-in-database (or timestamp) for each record in the database table and update the record based on that auto increment id.

If this is not possible then you can update the record based on all columns in the where clause with the previous value.

UPDATE NOMINATEDUSER SET @IDCHANNELUSER = newIDCHANNELUSERValue, NOMINATEDUSERID=@newNOMINATEDUSERIDValue WHERE IDCHANNELUSER = @PreviousIDCHANNELUSERVaLUE AND NOMINATEDUSERID = @previousNOMINATEDUSERIDValue

Hope this helps.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/1/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
You can disable trigger recursion by using RECURSIVE_TRIGGERS OFF at database level.

This will prevent a trigger fired to call another trigger or calling itself again. To do this, execute this command:

ALTER DATABASE DataBaseName SET RECURSIVE_TRIGGERS OFF
GO


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response