Quick sample code, not a complete solution but should be enough to get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_DETECT_DUPES') IS NOT NULL DROP TABLE dbo.TBL_DETECT_DUPES;

IF OBJECT_ID('dbo.SVFN_COUNT_DETECT_DUPES_CODE') IS NOT NULL DROP FUNCTION dbo.SVFN_COUNT_DETECT_DUPES_CODE;

DECLARE @FNC_STR NVARCHAR(MAX) = N'

CREATE FUNCTION dbo.SVFN_COUNT_DETECT_DUPES_CODE

(

@DD_ID INT

,@DD_CODE VARCHAR(10)

)

RETURNS INT

AS

BEGIN

RETURN

( SELECT

COUNT(*)

FROM dbo.TBL_DETECT_DUPES DD

WHERE DD.DD_ID < @DD_ID

AND DD.DD_CODE = @DD_CODE);

END

';

EXEC (@FNC_STR);

CREATE TABLE dbo.TBL_DETECT_DUPES

(

DD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_DETECT_DUPES_DD_ID PRIMARY KEY CLUSTERED

,DD_TSTAMP DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_DETECT_DUPES_DD_TSTAMP DEFAULT (GETDATE())

,DD_CODE VARCHAR(10) NOT NULL

,DD_COUNT AS (dbo.SVFN_COUNT_DETECT_DUPES_CODE(DD_ID,DD_CODE))

);

DECLARE @TRG_STR NVARCHAR(MAX) = N'

CREATE TRIGGER dbo.TRG_DUP_FOUND ON dbo.TBL_DETECT_DUPES

AFTER INSERT

AS

BEGIN

IF (SELECT COUNT(*) FROM inserted WHERE DD_COUNT > 0) > 0

BEGIN

/* Trigger action code */

SELECT * FROM inserted WHERE DD_COUNT > 0;

END

END

;';

EXEC (@TRG_STR);

INSERT INTO dbo.TBL_DETECT_DUPES (DD_CODE) VALUES ('ABC'),('DEF'),('GHI'),('JKL'),('MNO'),('PQR'),('STU'),('VWX');

INSERT INTO dbo.TBL_DETECT_DUPES (DD_CODE) VALUES ('ABC'),('VWX');

INSERT INTO dbo.TBL_DETECT_DUPES (DD_CODE) VALUES ('GHI'),('STU'),('VWX');

INSERT INTO dbo.TBL_DETECT_DUPES (DD_CODE) VALUES ('VWX');