Post reply

How to check existing values in a column for duplicates before inserting

  • August 12, 2014 at 6:34 am

    #289703

    HI there

    I have the following objective:

    1. I want to check a column to see if there are values (to Eliminate dups)

    2. Once checked the values in a column, if not found insert the new value

    Here is my code for this:

    ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl

    -- When inserting or updating

    AFTER INSERT, UPDATE AS

    -- Declare the variables

    DECLARE @an varchar(200)

    -- Set the variable to the value that has been inserted.

    SELECT @an = AlphanumericCol FROM inserted

    -- If the AlphanumericCol is not null

    IF @an IS NOT NULL

    BEGIN

    -- If a duplicate has been found, rollback trasaction and report error.

    IF EXISTS (

    SELECT 1

    FROM AMGR_User_Fields_Tbl

    GROUP BY AlphanumericCol

    HAVING COUNT(*) > 1

    )

    BEGIN

    RAISERROR ('Serial Number: %s already exists in the database. The transaction was rolled back',16,1, @an)

    ROLLBACK TRANSACTION

    END

    END

    Can someone please help me in fitting in the part where the check is made

  • August 12, 2014 at 6:54 am

    #1737191

    All the comments I made in the previous thread still stand.

    This should be an INSTEAD OF trigger, not an AFTER

    The trigger as written will not handle any insert or update that affects more than one row.

    http://www.sqlservercentral.com/Forums/Topic1601851-2799-1.aspx

    The check for dups isn't the problem here, it's that the trigger type is (probably) wrong and the trigger will not handle multi-row inserts as written (SQL triggers are statement triggers, not row triggers)

    First thing you need to decide. If someone inserts 10 rows and one of those rows is a duplicate of an existing row, what do you want to happen?

    - The transaction fails, no rows are inserted? In this case, an AFTER trigger is OK, but it still must handle multiple rows

    - The 9 non-duplicate rows are inserted and the duplicate row rejected? In this case you need an INSTEAD OF trigger

    A unique constraint or unique index would be easier still.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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