Hello friends, I don’t know what made me to write a blog, either it could be because i sit next to latheesh every day or inspired by reading his blogs daily, Any how, let me join with my Guru in sharing my experience.

we recently got a requirement to maintain the data integrity between two tables on a column which does not have any relationship between them.  Here is the scenario

we have two tables as listed below:

CREATE TABLE dbo.PEOPLE ( PEOPLE_ID INT ,EMPNAME VARCHAR(50), EMP_EMAIL VARCHAR(100))
CREATE TABLE dbo.USERS ( USERID INT ,USERNAME VARCHAR(50), USER_EMAIL VARCHAR(100))

INSERT INTO PEOPLE VALUES (1,'KIRAN','KIRANREDDY@GMAIL.COM')
INSERT INTO USERS VALUES (1,'KIRAN','KIRANREDDY2@GMAIL.COM')

I would like to maintain the same email in both tables for an employee, and email can be updated from either of these two tables.

So i have tried implementing the triggers on both tables when ever there is an update on email, I have to update the same email on both tables.

Here is the code I have implemented:

CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL)
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL)
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

When I tried updating the email from people, It has resulted the following error:

Msg 217, Level 16, State 1, Procedure upd_trg_users, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Solution :
Initially I have tried avoiding this error by changing the nested triggers Server Configuration Option

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'nested triggers', 0 ;  
GO  
RECONFIGURE;  
GO

Even though this error has not appeared when I tried changing email, I feel that this is not an Ideal solution . As this is server level configuration which is applicable across  all databases. This may have other detrimental impact.

Hence i have found another solution with simple tweak in the trigger code:

CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL) AND  EXISTS (SELECT 1 FROM USERS   U INNER JOIN INSERTED I ON U.USERID = I.PEOPLE_ID WHERE I.EMP_EMAIL <> U.USER_EMAIL )
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL) AND  EXISTS (SELECT 1 FROM PEOPLE   P INNER JOIN INSERTED I ON P.PEOPLE_ID=I.USERID  WHERE P.EMP_EMAIL <> I.USER_EMAIL )
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

Hope you enjoyed this post, please share your thoughts on the same.