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