UPDATE statement that creates duplicate records

  • Help! An UPDATE query seems to have inserted records into the target table in SQL Server 2000. Has anyone seen this before? I thought UPDATE statements only change current records ā€“ not add records.

    I set up a trigger on the UPDATE operation for a table A. Within the trigger, I issue an UPDATE statement so that any updates on certain fields will propagate to another related table B. The trigger fired upon the UPDATE operation on table A. The related table B got all of its records duplicated.

    Iā€™m wondering if anyone has some useful information based on this general description. If more detail is needed, I can add that.

    Bill Nicolich: www.SQLFave.com.
    Daily tweet of what's new and interesting: AppendNow

  • [font="Verdana"]Hi Bill,

    Can you provide the trigger code here?[/font]

  • Trigger Code (object names changed for anonymity):

    CREATE TRIGGER u_PartInformation ON PartInformation FOR UPDATE

    AS

    -- Propagate updates to description fields.

    IF UPDATE(part_num) OR UPDATE(headline_id) OR UPDATE(epd) OR UPDATE(catalog) OR UPDATE(color) OR UPDATE(application) OR UPDATE(short_desc) OR UPDATE(additional_info)

    BEGIN

    UPDATE [search].dbo.[ProductSearch]

    SET [search].dbo.[ProductSearch].[description] =

    ISNULL(ltrim(rtrim(i.part_num)),'') + ' ' +

    SUBSTRING(LTRIM(RTRIM(ISNULL(i.catalog,''))),3,LEN(LTRIM(RTRIM(ISNULL(i.catalog,''))))-2) + ' ' +

    SUBSTRING(LTRIM(RTRIM(ISNULL(b.[main_section],''))),4,46) + ' ' +

    SUBSTRING(LTRIM(RTRIM(ISNULL(c.[sub_section],''))),4,46) + ' ' +

    LTRIM(RTRIM(ISNULL(d.headline,''))) + ' ' +

    LTRIM(RTRIM(ISNULL(i.color,''))) + ' ' +

    LTRIM(RTRIM(ISNULL(i.application,''))) + ' ' +

    LTRIM(RTRIM(ISNULL(i.short_desc,''))) + ' ' +

    LTRIM(RTRIM(ISNULL(e.long_desc,''))) + ' ' +

    LTRIM(RTRIM(ISNULL(i.additional_info,''))),

    [search].dbo.[ProductSearch].[part_num] = i.part_num,

    [search].dbo.[ProductSearch].[headline_id] = i.headline_id,

    [search].dbo.[ProductSearch].[edp] = i.epd

    FROM

    deleted, inserted i

    LEFT OUTER JOIN main_section b

    ON LTRIM(RTRIM(i.[main_section_id])) = LTRIM(RTRIM(b.[main_section_id]))

    LEFT OUTER JOIN [sub_section] c

    ON LTRIM(RTRIM(i.[sub_section_id])) = LTRIM(RTRIM(c.[sub_section_id]))

    LEFT OUTER JOIN [headline] d

    ON LTRIM(RTRIM(i.[headline_id]))= LTRIM(RTRIM(d.[headline_id]))

    LEFT OUTER JOIN [long_description] e

    ON LTRIM(RTRIM(i.[long_desc_id])) = LTRIM(RTRIM(CAST(e.[long_desc_id] AS VARCHAR(8))))

    WHERE

    [search].dbo.[ProductSearch].part_num = i.part_num and [search].dbo.[ProductSearch].edp = i.epd

    AND (

    deleted.[catalog] <> i.[catalog]

    OR deleted. <> i.

    OR deleted.[application] <> i.[application]

    OR deleted.[short_desc] <> i.[short_desc]

    OR deleted.[additional_info] <> i.[additional_info]

    OR deleted.[part_num] <> i.[part_num]

    OR deleted.[headline_id] <> i.[headline_id]

    OR deleted.[epd] <> i.[epd]

    )

    END

    Bill Nicolich: www.SQLFave.com.
    Daily tweet of what's new and interesting: AppendNow

  • I think I see a dual triangular join between Deleted and Inserted and that might give you a funky rowcount... but I don't see anything in that code that would even come close to insert rows...

    What makes you thing the rows in TableB where duplicated? Did you do a before and after dupe check or are you just looking at row counts?

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

  • Have you got any triggers on Table B?

  • No triggers on table B.

    Bill Nicolich: www.SQLFave.com.
    Daily tweet of what's new and interesting: AppendNow

  • I actually verified that there were duplicates. The row count was unexpected. So I followd up with a query checking for duplicates - you know - with a GROUP BY clause on the key and a HAVING COUNT(*) > 1 clause.

    Bill Nicolich: www.SQLFave.com.
    Daily tweet of what's new and interesting: AppendNow

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

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