SQLTeam.com | Weblogs | Forums

Sql trigger after insert,update same table


My data is imported through the external system in to the SQLServer.
I want to write a trigger that allows all data to be added to the database by default unless they have special conditions to be updated(they must be updated)

I mean, we should search on all input records and if we have some duplicate data (based on the above conditions), the insert operation should not be performed and only the new input data should replace the old data(Update Action).

could you please help me?

Most questions about SQL Server can be answered with It depends... or with the question... What are you actually trying to solve?

In this case I think I would import the data from the external system into a new table first. Then I would create a stored procedure or even a SSIS project to process that data into the correct table. When a table is created properly it should not be possible to insert duplicate data.

If you really want to draw the Trigger card you should understand that it is hard to support for you and even harder for your co-workers and there are more issues. If you search for Brent Ozar and triggers you will find very well explained blogpost and video's but be sure to read

The Silent Bug I Find in Most Triggers - Brent Ozar Unlimited®
The Top 10 Developer Mistakes That Won't Scale on Microsoft SQL Server - YouTube

This is the actual answer to your question, please forgive me for the things I think you should consider before you create a trigger: I would use the Instead of insert trigger, explained very well in this article:
SQL Server Trigger Instead of Insert (mssqltips.com)