IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IM_ITEM' AND COLUMN_NAME = 'USR_HTML_DESCR')
BEGIN
ALTER TABLE IM_ITEM
ADD USR_HTML_DESCR T_HTML_DESCR NULL
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USR_EC_ITEM_DESCR_COPY_IM_ITEM]') AND type in (N'TR'))
EXEC ('CREATE TRIGGER [dbo].[USR_EC_ITEM_DESCR_COPY_IM_ITEM] ON EC_ITEM_DESCR AFTER INSERT, UPDATE as SELECT 1')
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Ray Knapp
-- Create date: 2020-04-24
-- Description: Copies HTML description to item table for export to ipaas.com
-- =============================================
ALTER TRIGGER dbo.USR_EC_ITEM_DESCR_COPY_IM_ITEM ON EC_ITEM_DESCR
AFTER INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- This checks if you are replicating and exits if you are
IF (Select dbo.fnReplicating()) = 'Y'
RETURN

-- exit if called by another procedure; prevents endless trigger loops
IF ((SELECT TRIGGER_NESTLEVEL() ) > 10 )
RETURN

UPDATE im_item SET USR_HTML_DESCR = EC_ITEM_DESCR.HTML_DESCR
FROM EC_ITEM_DESCR
Inner join inserted on inserted.item_no = EC_ITEM_DESCR.item_no
where im_item.item_no = EC_ITEM_DESCR.item_no

END -- End Trigger
GO