You might face a scenario where you’re using a REST Web Service or WebAPI where multiple hits of the same request to the API from the User interface is causing duplicate inserts. As the hits come to the API at the same date time-stamp, the API check fails at the database level as that record won’t exist in the table at that point in time.

To prevent the duplicate from getting inserted, we have multiple options at the Database level and one of them is using Instead Of trigger.

Using the INSTEAD Of trigger, you can conditionally choose to INSERT into the table or take some other action as per the requirement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PREVENT_DUP_INSERT] ON [dbo].[tbl_submission]
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT OFF;
	IF NOT EXISTS(
		SELECT 1 FROM dbo.tbl_submission
		WHERE question_uid=(SELECT inserted.q_uid FROM inserted)
		AND user_survey_instance_id=(SELECT user_inst_id FROM inserted)
	)
	BEGIN
		INSERT INTO dbo.tbl_submission(user_id,q_uid,[value],[group],user_inst_id,created_at,updated_at)
		SELECT user_id,q_uid,[value],[group],user_inst_id,created_at,updated_at FROM inserted
	END
	SELECT [id] FROM [dbo].[tbl_submission] WHERE @@ROWCOUNT > 0 AND [id] = scope_identity();
END

An id must be returned by the body of the INSTEAD OF trigger. This is required especially if you’re using an ORM like Entity Framework which may be give concurrency related exception.

You can also choose to apply UNIQUE constraint on select columns to prevent duplicates and handle the Insert exceptions in the API or Trigger itself. But in my case, since there are already few duplicate insertions and one of the columns was VARCHAR(MAX) which does not allow creating UNIQUE indexes.