1
Answer

How to prevent recursion of update trigger on the same table

Bhanuprakash Bysani

Bhanuprakash Bysani

9y
911
1

I have a requirement where the table contains the values like below.

UniqueId custid mobileno emailid profileId
1 101 9440765465 [email protected] 120
2 101 9440766666 [email protected] 121
3 102 9555222222 [email protected] 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)