--You must change which price field is used. The code below uses PRC_2
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IM_ITEM' AND COLUMN_NAME = 'USR_IPAAS_PRC')
BEGIN
ALTER TABLE IM_ITEM
ADD USR_IPAAS_PRC T_PRC NULL
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USR_IPAAS_COPY_IM_PRC]') AND type in (N'TR'))
EXEC ('CREATE TRIGGER [dbo].[USR_IPAAS_COPY_IM_PRC] ON IM_PRC 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 PRC_2 field from IM_PRC to item table for export to ipaas.com
-- =============================================
ALTER TRIGGER dbo.USR_IPAAS_COPY_IM_PRC ON IM_PRC
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_IPAAS_PRC = IM_PRC.PRC_2
FROM IM_PRC
Inner join inserted on inserted.item_no = IM_PRC.item_no
where im_item.item_no = IM_PRC.item_no
END -- End Trigger
GO