SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE statement that creates duplicate records


UPDATE statement that creates duplicate records

Author
Message
Bill Nicolich
Bill Nicolich
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 545
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
vidhya sagar
vidhya sagar
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4090 Visits: 620
Hi Bill,

Can you provide the trigger code here?


Regards..Vidhya Sagar
SQL-Articles
Bill Nicolich
Bill Nicolich
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 545
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)SSC Guru (428K reputation)

Group: General Forum Members
Points: 428297 Visits: 43436
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ian Scarlett
Ian Scarlett
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10497 Visits: 7233
Have you got any triggers on Table B?



Bill Nicolich
Bill Nicolich
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 545
No triggers on table B.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Bill Nicolich
Bill Nicolich
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 545
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search