Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE statement that creates duplicate records Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2008 8:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
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
Posted Wednesday, June 25, 2008 11:43 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:31 PM
Points: 416, Visits: 573
Hi Bill,

Can you provide the trigger code here?


Regards..Vidhya Sagar
SQL-Articles
Post #523874
Posted Thursday, June 26, 2008 8:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
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
Posted Thursday, June 26, 2008 9:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #524730
Posted Friday, June 27, 2008 1:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
Have you got any triggers on Table B?


Post #524798
Posted Friday, June 27, 2008 6:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
No triggers on table B.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #524899
Posted Friday, June 27, 2008 6:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
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 »

Add to briefcase

Permissions Expand / Collapse