SQL Server Trigger to prevent insertion of duplicate data in Table

Question

Post reply

SQL Server Trigger to prevent insertion of duplicate data in Table

sabarishbabu

Old Hand

Points: 371

June 29, 2020 at 9:47 pm

Go to Answer

#3766311

Hello,

I have a company table for some reason I can't create a constraint on table level. I am trying to use the following Trigger on my table to avoid duplicate records. when I try to insert the record I am getting following error. Please advise how to avoid duplicate record insert / Update using Triggers.

Trigger :

CREATE TRIGGER [dbo].[trg_Company]
ON [dbo].[Company]
FOR INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @ErrorMessage VARCHAR(MAX),
            @CompanyID INT,
            @CompanyName VARCHAR(50);

    -- Get the CompanyID  and CompanyName value from the magic table
    --
    SELECT @CompanyName = INSERTED.CompanyName
    FROM INSERTED;

    SELECT @CompanyID = INSERTED.CompanyID
    FROM INSERTED;

    -- Validate the record exists on the [dbo].[Company] table 
    --
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[Company]
        WHERE [CompanyName] = @CompanyName
              OR CompanyID = @CompanyID
    )
    BEGIN
        SET @ErrorMessage
            = CONCAT(
                        '[dbo].[Company] Insert - Record CompanyID=' + CAST(@CompanyID AS VARCHAR)
                        + ' Or @CompanyName=' + @CompanyName + ' already exists.',
                        REPLACE(ERROR_MESSAGE(), '''', '"')
                    );

        RAISERROR(@ErrorMessage, 16, 1);

        --Rollback and end the transaction inside the trigger   
        ROLLBACK TRANSACTION;
    END;
END;

ErrrorMessage:

Msg 50000, Level 16, State 1, Procedure trg_Company, Line 39 [Batch Start Line 11]
[dbo].[Company] Insert - Record CompanyID=1 Or @CompanyName=AAA already exists.
Msg 3609, Level 16, State 1, Line 12
The transaction ended in the trigger. The batch has been aborted.

Phil Parkin

SSC Guru

Points: 246960
More actions
June 30, 2020 at 4:24 pm
Answer
#3766489
If you were to add a unique filtered index on the CompanyName column, this would prevent the insertion of duplicate non-NULL company names, while allowing multiple NULLs:
```
CREATE UNIQUE NONCLUSTERED INDEX UX_CompanyName
ON dbo.Company (CompanyName)
WHERE CompanyName IS NOT NULL;
```
However, if the desire is to prevent such duplicates without firing an error, the trigger is probably the way to go. (I do whatever I can to avoid triggers!)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Please explain this statement:

I have a company table for some reason I can't create a constraint on table level

The constraint is the way to go. Tell us what you tried and why there is a problem so that we can help you get the constraint in place.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sabarishbabu Old Hand Points: 371 More actions · Answer 2

Thanks Phil !! If I add new constraints it will blocking some existing system. so I can't create any constraint on this table.

In addition, the Company table having multiple columns and the CompanyID column as derived column with PK , however CompanyName some times come as NULL value. I would like to insert only the unique CompanyName into Company Table.

Company

Above one is the sample data and I don't want to insert the duplicate company name.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

First, you trigger must be INSTEAD OF, not FOR.

FOR triggers are executed after insert is done, so, it there is a constraint - it will raise the error before the trigger is executed.

Second, if I understand you explanation right, you want to avoid duplicate names where they are NOT NULL, and if inserted name is NULL then exclude duplicate CompanyID - is it right?

If yes, then your filter must look like this:

 IF EXISTS
(
SELECT 1
FROM [dbo].[Company] C

INNER JOIN inseted i ON i.[CompanyName] = C.CompanyName 
OR (i.[CompanyName] IS NULL AND C.CompanyName IS NULL AND i.CompanyID = C.CompanyID)

Not sure how you want to deal with cases when 2 records have the same CompanyID, but 1 of them has CompanyName NULL. To me - it's duplicate as well, but it's OK according to your definitions above.

_____________
Code for TallyGenerator

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Also, triggers are processed for the entire result set at once. You've set up your trigger to only process one row of the result set and discard the other rows. You should join to the INSERTED/DELETED tables rather than assigning their values to scalar variables.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

I don't know if it will help in this case but you CAN write a unique index with an "Ignore Duplicates" attribute that will simply not insert duplicates and won't cause a failure if you try.

However, that's not the right way to do such things. The right way is to have the index (constraint) fail and return the failure to the front end so that people on the front end know something is wrong instead of it simply continuing with a silent ignore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)