June 25, 2008 at 8:17 am
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
June 25, 2008 at 11:43 pm
[font="Verdana"]Hi Bill,
Can you provide the trigger code here?[/font]
Regards..Vidhya Sagar
SQL-Articles
June 26, 2008 at 8:43 am
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
June 26, 2008 at 9:40 pm
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
Change is inevitable... Change for the better is not.
June 27, 2008 at 1:16 am
Have you got any triggers on Table B?
June 27, 2008 at 6:01 am
No triggers on table B.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
June 27, 2008 at 6:03 am
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