SQL Server Trigger to prevent insertion of duplicate data in Table

  • sabarishbabu

    Old Hand

    Points: 346

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

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • sabarishbabu

    Old Hand

    Points: 346

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

    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.

  • drew.allen

    SSC Guru

    Points: 76737

    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

  • Phil Parkin

    SSC Guru

    Points: 244589

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 996661

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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