[SOLVED] Problem Double Entry Because Trigger After Insert In SQL Server

Posted on by

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!

Leave a Reply