Recently I got a problem with trigger in SQL Server, the trigger I made was provoke an anomaly. The goal is after insert a data in a table, I need to update a column within it with a external data.
The problem when insert 1 row data, it generates 2 row with the exactly same data. I tried to remove the trigger I created before, and insert a new data again, the problem is gone. Here it is my trigger source code:
CREATE TRIGGER [dbo].[TEST01] ON [01_APD].[dbo].['MVCELLGI'] AFTER INSERT AS BEGIN DECLARE @EXTERNALREFID NVARCHAR(255), @SADDRESSCODE NVARCHAR(255), @ID_MVCELLGI INT SET @ID_MVCELLGI = (SELECT ID_MVCELLGI FROM INSERTED) SET @SADDRESSCODE = (SELECT T2.[SADDRESSCODE] FROM [01_APD].dbo.['MVCELLGI'] T1 JOIN [01_APD].dbo.[HELP_01_SADDRESSCODE] T2 ON T1.EXTERNALREFID_LOCATION=T2.[EXTERNALREFID] WHERE T1.ID_MVCELLGI = @ID_MVCELLGI) UPDATE dbo.['MVCELLGI'] SET [SADDRESSCODE] = @SADDRESSCODE WHERE ID_MVCELLGI = @ID_MVCELLGI -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; END
I think the code have no fault, I did some research and got the answer. So simple, just run this code once:
ALTER DATABASE [01_APD] SET RECURSIVE_TRIGGERS OFF
Here the result after fixing the problem:
I hope it will be help you out as well someday, cheers!