Here is somewhat a generic sample, should get you passed this hurdle. This is not a complete solution but rather a demonstration of the technique.
😎
First the schema and the sample data
USE tempdb;
GO
/* TEST DDL AND SAMPLE DATA*/
CREATE TABLE dbo.THE_INSERT_TABLE
(
TIT_ID INT NOT NULL
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.FIRST_UPDATE_TABLE
(
TIT_ID INT NOT NULL CONSTRAINT PK_DBO_FIRST_UPDATE_TABLE_TIT_ID
PRIMARY KEY CLUSTERED
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.SECOND_DELETE_TABLE
(
TIT_ID INT NOT NULL CONSTRAINT PK_DBO_SECOND_DELETE_TABLE_TIT_ID
PRIMARY KEY CLUSTERED
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
INSERT INTO dbo.THE_INSERT_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(10,'A10','B10')
,(11,'A11','B11')
,(12,'A12','B12')
,(13,'A13','B13')
,(14,'A14','B14')
,(15,'A15','B15');
INSERT INTO dbo.FIRST_UPDATE_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(90,'A10','B10')
,(91,'A11','B11')
,(92,'A12','B12')
,(93,'A13','B13')
,(94,'A14','B14')
,(95,'A15','B15');
INSERT INTO dbo.SECOND_DELETE_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(20,'A10','B10')
,(31,'A11','B11')
,(42,'A12','B12')
,(53,'A13','B13')
,(64,'A14','B14')
,(75,'A15','B15');
And then the trigger code
CREATE TRIGGER dbo.TRG_DELETE_DUPE_ON_INSERT
ON dbo.THE_INSERT_TABLE
AFTER INSERT
AS
BEGIN
DELETE T
FROM dbo.THE_INSERT_TABLE T
INNER JOIN inserted I
ON T.TIT_STR1 = I.TIT_STR1
AND T.TIT_STR2 = I.TIT_STR2
INSERT INTO dbo.THE_INSERT_TABLE
(TIT_ID,TIT_STR1,TIT_STR2)
SELECT TIT_ID,TIT_STR1,TIT_STR2
FROM inserted
UPDATE F
SET F.TIT_ID = I.TIT_ID
FROM inserted I
INNER JOIN dbo.FIRST_UPDATE_TABLE F
ON I.TIT_STR1 = F.TIT_STR1
AND I.TIT_STR2 = F.TIT_STR1
DELETE X
FROM inserted I
INNER JOIN dbo.SECOND_DELETE_TABLE X
ON I.TIT_ID = X.TIT_ID
INSERT INTO dbo.SECOND_DELETE_TABLE
(TIT_ID,TIT_STR1,TIT_STR2)
SELECT TIT_ID,TIT_STR1,TIT_STR2
FROM inserted
END
Finally a small test
SELECT * FROM dbo.THE_INSERT_TABLE;
SELECT * FROM dbo.FIRST_UPDATE_TABLE;
SELECT * FROM dbo.SECOND_DELETE_TABLE;
INSERT INTO dbo.THE_INSERT_TABLE (TIT_ID,TIT_STR1,TIT_STR2) VALUES (101,'A11','B11');
SELECT * FROM dbo.THE_INSERT_TABLE;
SELECT * FROM dbo.FIRST_UPDATE_TABLE;
SELECT * FROM dbo.SECOND_DELETE_TABLE;
"After" Results
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
10 A10 B10
11 A11 B11
12 A12 B12
13 A13 B13
14 A14 B14
15 A15 B15
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
90 A10 B10
91 A11 B11
92 A12 B12
93 A13 B13
94 A14 B14
95 A15 B15
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
20 A10 B10
31 A11 B11
42 A12 B12
53 A13 B13
64 A14 B14
75 A15 B15
101 A11 B11