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.
SQLZealots 