Bhanuprakash Bysani

Bhanuprakash Bysani

  • 1.7k
  • 338
  • 20.2k

How to prevent recursion of update trigger on the same table

Jun 30 2015 10:15 PM

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.


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




GO

Please help me how to prevent recursion.


Answers (1)