S-I-M-P-L-I-F-Y 😉

I don't guarantee this will work because I don't have your tables to test it on but it should be pretty close. The key here is (as it says in my signature line below) to stop thinking in rows and start thinking in columns.

ALTER TRIGGER [dbo].[TR_NAME_UDF_LABELS] ON [dbo].[CUSTOMER]

AFTER INSERT, UPDATE

AS

--===== Prevent trigger feedback loop

IF TRIGGER_NESTLEVEL() > 1 RETURN

--===== Add the configuration rows for each CustID that

-- doesn't already have them.

INSERT INTO dbo.MetaColumn_Configuration

(CustID,MetaColumnID,Label)

SELECT i.CustID, cj.MetaColumnID, cj.Label

FROM INSERTED i

CROSS JOIN

(

SELECT 146,'Customer Name' UNION ALL

SELECT 150,'MOVE ID#' UNION ALL

SELECT 151,'LOT#' UNION ALL

SELECT 152,'DATE IN' UNION ALL

SELECT 153,'STORAGE TYPE' UNION ALL

SELECT 154,'EARLY OUT' UNION ALL

SELECT 309,'AUTO' UNION ALL

SELECT 310,'DEL BY INTO STGE' UNION ALL

SELECT 311,'EQUIPMENT #' UNION ALL

SELECT 312,'UDF10' UNION ALL

SELECT 313,'UDF11' UNION ALL

SELECT 314,'UDF12'

) cj (MetaColumnID,Lable)

WHERE NOT EXISTS (SELECT CustID FROM dbo.MetaColumn_Configuration mc WHERE mc.CustID = i.CustID)

AND i.BillingType IN ('HOUSEHOLD', 'HOUSEHOLD PERMANENT', 'HOUSEHOLD SIT')

;

--===== Update the prompts in the customer table to enable prompting after we've configured the customer.

-- These shouldn't actually be in the trigger. They should be defaults in the table.

UPDATE c

SET UDF1PROMPT = 1,

UDF2PROMPT = 1,

UDF3PROMPT = 1,

UDF4PROMPT = 1,

UDF5PROMPT = 1,

UDF6PROMPT = 1,

UDF7PROMPT = 1,

UDF8PROMPT = 1,

UDF9PROMPT = 1

FROM dbo.Customer c

JOIN INSERTED i

ON c.CustID = i.CustID

;

The update for the UDFxPROMPT columns shouldn't be in the trigger. They should be defaults in the table.

Also, get into the habit of almost always (and there are incredibly few and very rare reasons to do otherwise) using the 2 part naming convention and setup whatever you're using to write code with to convert tabs to spaces.