SQL Server Trigger to prevent insertion of duplicate data in Table

  • 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]

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

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

    SELECT @CompanyID = INSERTED.CompanyID

    -- Validate the record exists on the [dbo].[Company] table
    SELECT 1
    FROM [dbo].[Company]
    WHERE [CompanyName] = @CompanyName
    OR CompanyID = @CompanyID
    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


    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.
  • 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.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • 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.


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



  • 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:

    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

  • 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.



    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • 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:

    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!)

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • 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)
    Intro to Tally Tables and Functions

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

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