UPDATE statement that creates duplicate records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » UPDATE statement that creates duplicate...

UPDATE statement that creates duplicate records

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Nicolich

Posted Wednesday, June 25, 2008 8:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111, Visits: 534

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

Post #523395

vidhya sagar

Posted Wednesday, June 25, 2008 11:43 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:45 AM
Points: 416, Visits: 521

Hi Bill,

Can you provide the trigger code here?

Regards..Vidhya Sagar
SQL-Articles

Post #523874

Bill Nicolich

Posted Thursday, June 26, 2008 8:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111, Visits: 534

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.[color] <> i.[color]
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

Post #524251

Jeff Moden

Posted Thursday, June 26, 2008 9:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #524730

Ian Scarlett

Posted Friday, June 27, 2008 1:16 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 1,322, Visits: 4,400

Have you got any triggers on Table B?

Post #524798

Bill Nicolich

Posted Friday, June 27, 2008 6:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111, Visits: 534

No triggers on table B.

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

Post #524899

Bill Nicolich

Posted Friday, June 27, 2008 6:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111, Visits: 534

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

Post #524901

« Prev Topic | Next Topic »

Permissions