December 2, 2014 at 10:34 pm
Hi,
Recently I got a task to find for the duplicate records on a table.
And I have written the below query and it gave correct result set :
Select Employee_Code,COUNT(*) from Employee
Group by Employee_Code
HAVING COUNT(*) > 1
Now they have come back to me and asked if they can get an email whenever there is a duplicate entry entered on the Employee table.
I am not sure how to write a trigger for the above process including sending an email to the recipients.
We are using SQL SERVER 2005.
Any ideas?
Is writing a trigger the only solution or is there any other alternative?
Thanks
December 2, 2014 at 11:59 pm
Why not create a UNIQUE constraint on the table and disallow the duplicates that way?
December 2, 2014 at 11:59 pm
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');
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply