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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Ray Knapp
-- Create date: 2020-05-12
-- Description: Converts barcodes to JSON and copies to JSON field in IM_ITEM table.
-- =============================================
ALTER TRIGGER dbo.USR_COPY_BARCOD_JSON ON IM_BARCOD
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_BARCODES_JSON = [barcodes].barcod_json
FROM (
select inserted.item_no,
'['+
REPLACE(
REPLACE(
REPLACE(
REPLACE(
(select barcod_id, barcod from im_barcod where item_no = inserted.item_no and barcod_id is not null
for xml raw)
,'/><row barcod_id=','},{"type": ')
,'<row barcod_id=','{"type": ')
,'barcod=',', "value": ')
,'/>','}')
+']'
as barcod_json
from inserted inner join im_barcod
on inserted.item_no = im_barcod.item_no
) [barcodes]
WHERE IM_ITEM.ITEM_NO = [barcodes].ITEM_NO
END -- End Trigger
GO