CODEWITHSUNDEEP
sql-serverssis
subbu2464
subbu2464

Reputation: 3

How to write a trigger on a table when duplicate records are inserting and then insert those records into anothe table

I am doing VB load and SSIS load. So that from these loads data is inserting into database tables. I need to find if there is any duplicate records are inserting into the tables. If any duplicate records are going into the table then i need to fire a trigger at the insertion of duplicate record and i need to insert the same record into another table.

For example y VB load or SSIS load is inserting records into the Table1. So i need to find if any duplicate records are inserting into table1. If inserted then i need to fire a trigger and need to insert that duplicate record into Table2.

Please suggest the solution. Your help really appreciated. Thanks in advance

Upvotes: 0

Views: 694

Answers (2)

StuartLC
StuartLC

Reputation: 107387

Here's an example of the instead of trigger. I've assumed you only want one copy of each duplicate. (I've assumed 'Name' is the only key used in LoadTable for detection of duplicates)

CREATE TRIGGER tCheckDupes ON LoadTable INSTEAD OF INSERT
AS BEGIN
    INSERT INTO DupesTable(Name)
        SELECT DISTINCT i.Name FROM INSERTED i
            INNER JOIN LoadTable lt
            ON lt.NAME = i.NAME;

    -- Because this is an instead of trigger, you need to do the actual insert as well.
    INSERT INTO LoadTable(Name)
        SELECT Name FROM INSERTED i;
END

Fiddle here

Upvotes: 0

lbarreira
lbarreira

Reputation: 170

Your best option is to create a INSTEAD OF INSERT trigger on the table.

Inside the trigger you can check if the record already exists and insert it on another table or proceed with the insert.

Upvotes: 1

Related Questions