I have a requirement where the table contains the values like below.
UniqueId custid mobileno emailid profileId
1 101 9440765465 firstname.lastname@example.org 120
2 101 9440766666 email@example.com 121
3 102 9555222222 firstname.lastname@example.org 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
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
Please help me how to prevent recursion.