ALTER TRIGGER [dbo].[TG_UPDATE_MBR_DEMOGRAPHICS] ON [dbo].[ER100_ACCT_ORDER]
AFTER UPDATE
AS
BEGIN
DECLARE	@ORG_CODE varchar(2)
SET		@ORG_CODE = (SELECT i.ER100_ORG_CODE FROM inserted i WITH (NOLOCK))

DECLARE	@EXT_ACCT_CODE varchar(10)
SET		@EXT_ACCT_CODE = (SELECT i.ER100_ORD_ACCT FROM inserted i WITH (NOLOCK))

DECLARE @ORD_TYPE varchar(2)
SET		@ORD_TYPE = (SELECT i.ER100_ORD_TYPE FROM inserted i WITH (NOLOCK))

DECLARE @ORD_NBR int
SET		@ORD_NBR = (SELECT i.ER100_ORD_NBR FROM inserted i WITH (NOLOCK))

DECLARE @ORD_DUE numeric(11,2)
SET		@ORD_DUE = (SELECT i.ER100_ACT_DUE FROM inserted i WITH (NOLOCK))
								 
DECLARE	@ORD_STS varchar(1)
SET		@ORD_STS = (SELECT i.ER100_NEW_STS FROM inserted i WITH (NOLOCK))

DECLARE	@RES_CODE varchar(12)
SET		@RES_CODE = (SELECT MAX(ER101_RES_CODE) FROM	ER101_ACCT_ORDER_DTL WITH (NOLOCK) 
												WHERE	ER101_ORG_CODE = @ORG_CODE AND ER101_ORD_NBR = @ORD_NBR AND 
														ER101_PHASE = '5' AND ER101_RES_CODE in ('AMABSENT', 'SDABSENT'))
														
DECLARE @NEW_MBR_CATEGORY varchar(6)
SET		@NEW_MBR_CATEGORY = (SELECT MRC02_MBR_CATEGORY FROM MRC02_CARD_NBR_MSTR WITH (NOLOCK) WHERE MRC02_RES_CODE = @RES_CODE)
	
DECLARE @NEW_MBR_TYPE varchar(6)
SET		@NEW_MBR_TYPE = (SELECT MRC02_MEMBER_TYPE FROM MRC02_CARD_NBR_MSTR WITH (NOLOCK) WHERE MRC02_RES_CODE = @RES_CODE)

DECLARE @MBR_STS varchar(4)
SET		@MBR_STS = (SELECT EV870_SALES_REGION	FROM	EV870_ACCT_MASTER WITH (NOLOCK)
												WHERE	EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE)													

	-- FOR CLOSED, FULLY PAID ABSENTEE MEMBERSHIP ORDERS
	IF @ORD_STS = 'C' AND @ORD_DUE <= 0 AND @ORD_TYPE = 'MB' AND (@RES_CODE in ('AMABSENT', 'SDABSENT'))
	BEGIN
		-- UPDATE DESIGNATION
			UPDATE	EV870_ACCT_MASTER
			SET		EV870_MEMBER_DESIG =  '1'
			WHERE	EV870_MEMBER_DESIG <> '1' AND EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE
			
		-- UPDATE DESIGNATION STATUS
			UPDATE	EV870_ACCT_MASTER
			SET		EV870_MEMBER_STS =  'A'
			WHERE	EV870_MEMBER_STS <> 'A' AND EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE	
			
		-- UPDATE AR SECURITY CODE
			UPDATE	EV870_ACCT_MASTER
			SET		EV870_ACCT_SECURITY =  'MBR'
			WHERE	EV870_ACCT_SECURITY <> 'MBR' AND EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE
			
		-- UPDATE MARKET SEGMENT
			UPDATE	EV870_ACCT_MASTER
			SET		EV870_MKT_SEG_1 = 'M', EV870_MKT_SEG_2 = 'MEM'
			WHERE	EV870_MKT_SEG_2 <> 'MEM' AND EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE
			
		-- UPDATE MEMBERSHIP STATUS
			UPDATE	EV885_MEMBER_INFO
			SET		EV885_MEMBER_STS =  'A'
			WHERE	EV885_MEMBER_STS <> 'A' AND EV885_ORG_CODE = @ORG_CODE AND EV885_EXT_ACCT_CODE = @EXT_ACCT_CODE
						
		-- UPDATE MEMBER TYPE
			UPDATE	EV885_MEMBER_INFO
			SET		EV885_MEMBER_TYPE = @NEW_MBR_TYPE, EV885_MBR_TYPE_DATE = GETDATE()
			WHERE	EV885_MEMBER_TYPE <> @NEW_MBR_TYPE AND  
					EV885_ORG_CODE = @ORG_CODE AND EV885_EXT_ACCT_CODE = @EXT_ACCT_CODE
			
		-- UPDATE MEMBER CATEGORY
			UPDATE	EV885_MEMBER_INFO
			SET		EV885_MBR_CATEGORY = @NEW_MBR_CATEGORY, EV885_MBR_CAT_DATE = GETDATE()
			WHERE	EV885_MBR_CATEGORY <> @NEW_MBR_CATEGORY AND 
					EV885_ORG_CODE = @ORG_CODE AND EV885_EXT_ACCT_CODE = @EXT_ACCT_CODE
	
		-- UPDATE STATUS TYPE (SALES REGION)
			IF @MBR_STS IN ('NF', 'OF', 'LP', 'WT')
			BEGIN
				UPDATE	EV870_ACCT_MASTER
				SET		EV870_SALES_REGION = (CASE	WHEN @MBR_STS = 'OF' THEN 'NW'
													WHEN @MBR_STS = 'WT' THEN 'NW'
													WHEN @MBR_STS = 'NF' THEN 'RW'
													WHEN @MBR_STS = 'LP' THEN 'RW' 
												END)
				WHERE	EV870_ORG_CODE = @ORG_CODE AND EV870_ACCT_CODE = @EXT_ACCT_CODE
			END	
	END
END

DECLARE	@ORG_CODE varchar(2)
SET		@ORG_CODE = (SELECT i.ER100_ORG_CODE FROM inserted i WITH (NOLOCK))

DECLARE	@EXT_ACCT_CODE varchar(10)
SET		@EXT_ACCT_CODE = (SELECT i.ER100_ORD_ACCT FROM inserted i WITH (NOLOCK))

-- UPDATE DESIGNATION
UPDATE	EV870_ACCT_MASTER
SET		EV870_MEMBER_DESIG =  '1'
WHERE	EV870_MEMBER_DESIG <> '1' 
	AND EV870_ORG_CODE = @ORG_CODE 
	AND EV870_ACCT_CODE = @EXT_ACCT_CODE

UPDATE	e
SET		EV870_MEMBER_DESIG =  '1'
FROM	EV870_ACCT_MASTER e
		INNER JOIN INSERTED i ON 
			e.EV870_ORG_CODE = i.ER100_ORG_CODE 
			AND e.EV870_ACCT_CODE = i.ER100_ORD_ACCT
WHERE	
	e.EV870_MEMBER_DESIG <> '1';

quote:

---

Originally posted by Ted75

Also, I am using a trigger with similar variable declarations to pass these to a stored procedure. I need to be able to pass values from each record in the INSERTED table to the SP. How do I do that? Sample code below. (Help really appreciated)

ALTER TRIGGER [dbo].[TG_ASSIGN_CARDS_ISSUE_UPDATE] ON [dbo].[CR073_ISSUES]
AFTER UPDATE
AS
BEGIN
DECLARE @ORG_CODE varchar(2)
SET @ORG_CODE = (SELECT CR073_ORG_CODE FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_CLASS char(1)
SET @ISSUE_CLASS = (SELECT CR073_ISSUE_CLASS FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_STS varchar(1)
SET @ISSUE_STS = (SELECT CR073_ISSUE_STS FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_TYPE varchar(2)
SET @ISSUE_TYPE = (SELECT CR073_ISSUE_TYPE FROM inserted WITH (NOLOCK))

DECLARE @ORD_NBR int
SET @ORD_NBR = (SELECT CR073_ORD_NBR FROM inserted WITH (NOLOCK))

DECLARE @UPDATE_FLAG varchar(3)
SET @UPDATE_FLAG = (SELECT CR073_TXT_02 FROM inserted WITH (NOLOCK))

-- ONLY FOR MEMBERSHIP CARD ISSUE TYPES
IF @ISSUE_CLASS = 'M' AND @ISSUE_TYPE IN (SELECT MRC02_ISSUE_TYPE
FROM MRC02_CARD_NBR_MSTR WITH (NOLOCK))
AND @UPDATE_FLAG = '002'
BEGIN

DECLARE @ISSUE_DESC varchar(255)
SET @ISSUE_DESC = (SELECT CR073_ISSUE_DESC FROM inserted WITH (NOLOCK))

DECLARE @EXT_ACCT_CODE varchar(10)
SET @EXT_ACCT_CODE = (SELECT CR073_EXT_ACCT_CODE FROM inserted WITH (NOLOCK))

DECLARE @OCCURENCE int
SET @OCCURENCE = (SELECT CR073_OCCURENCE FROM inserted WITH (NOLOCK))

DECLARE @USER_ID varchar(10)
SET @USER_ID = (SELECT CR073_UPD_USER_ID FROM inserted WITH (NOLOCK))

DECLARE @CURRENT_CARD_NUM varchar(10)
SET @CURRENT_CARD_NUM = (SELECT CR073_ISSUE_LOG_ID FROM inserted WITH (NOLOCK))

DECLARE @ORD_TOT numeric(11,2)
SET @ORD_TOT = (SELECT ER100_ACT_TOTAL FROM ER100_ACCT_ORDER WITH (NOLOCK) WHERE ER100_ORD_NBR = @ORD_NBR)

DECLARE @ORD_TAX numeric(11,2)
SET @ORD_TAX = (SELECT ER100_ACT_TAX FROM ER100_ACCT_ORDER WITH (NOLOCK) WHERE ER100_ORD_NBR = @ORD_NBR)

DECLARE @RES_TYPE varchar(6)
SET @RES_TYPE = (SELECT CR073_TXT_04 FROM inserted WITH (NOLOCK))

DECLARE @RES_CODE varchar(12)
SET @RES_CODE = (SELECT CR073_TXT_05 FROM inserted WITH (NOLOCK))

EXEC sp_Assign_Cards_Update
@ORG_CODE, @EXT_ACCT_CODE, @OCCURENCE, @ISSUE_CLASS, @ISSUE_STS,
@ISSUE_TYPE, @USER_ID, @UPDATE_FLAG, @CURRENT_CARD_NUM, @ISSUE_DESC,
@ORD_NBR, @ORD_TOT, @ORD_TAX, @RES_TYPE, @RES_CODE
END
END

---

quote:

---

Originally posted by Deepak S

At any point of time, INSERTED will have only one record at a time. So, your current trigger should take care of it.

Deepak S

---

create table #foo (ar int, gb char(1))
create table #bar (ar int, gb char(1))

insert into #foo
output inserted.ar,inserted.gb
	into #bar
values (1,'3'),(2,'f'),(6,'p')

select * from #bar

quote:

---

Originally posted by Ted75

Many thanks. I understand what you have done. This is fine for the update statements but how do I incorporate this logic into the IF BEGIN statements? I only need the trigger to run for particular records.

---

UPDATE	e
SET		EV870_MEMBER_DESIG =  '1'
FROM	EV870_ACCT_MASTER e
		INNER JOIN INSERTED i ON 
			e.EV870_ORG_CODE = i.ER100_ORG_CODE 
			AND e.EV870_ACCT_CODE = i.ER100_ORD_ACCT
WHERE	
	e.EV870_MEMBER_DESIG <> '1'
	AND i.ER100_NEW_STS = 'C'
	AND i.ER100_ACT_DUE <= 0
	AND i.ER100_ORD_TYPE = 'MB'

quote:

---

Originally posted by Ted75

Also, I am using a trigger with similar variable declarations to pass these to a stored procedure. I need to be able to pass values from each record in the INSERTED table to the SP. How do I do that? Sample code below. (Help really appreciated)

ALTER TRIGGER [dbo].[TG_ASSIGN_CARDS_ISSUE_UPDATE] ON [dbo].[CR073_ISSUES]
AFTER UPDATE
AS
BEGIN
DECLARE @ORG_CODE varchar(2)
SET @ORG_CODE = (SELECT CR073_ORG_CODE FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_CLASS char(1)
SET @ISSUE_CLASS = (SELECT CR073_ISSUE_CLASS FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_STS varchar(1)
SET @ISSUE_STS = (SELECT CR073_ISSUE_STS FROM inserted WITH (NOLOCK))

DECLARE @ISSUE_TYPE varchar(2)
SET @ISSUE_TYPE = (SELECT CR073_ISSUE_TYPE FROM inserted WITH (NOLOCK))

DECLARE @ORD_NBR int
SET @ORD_NBR = (SELECT CR073_ORD_NBR FROM inserted WITH (NOLOCK))

DECLARE @UPDATE_FLAG varchar(3)
SET @UPDATE_FLAG = (SELECT CR073_TXT_02 FROM inserted WITH (NOLOCK))

-- ONLY FOR MEMBERSHIP CARD ISSUE TYPES
IF @ISSUE_CLASS = 'M' AND @ISSUE_TYPE IN (SELECT MRC02_ISSUE_TYPE
FROM MRC02_CARD_NBR_MSTR WITH (NOLOCK))
AND @UPDATE_FLAG = '002'
BEGIN

DECLARE @ISSUE_DESC varchar(255)
SET @ISSUE_DESC = (SELECT CR073_ISSUE_DESC FROM inserted WITH (NOLOCK))

DECLARE @EXT_ACCT_CODE varchar(10)
SET @EXT_ACCT_CODE = (SELECT CR073_EXT_ACCT_CODE FROM inserted WITH (NOLOCK))

DECLARE @OCCURENCE int
SET @OCCURENCE = (SELECT CR073_OCCURENCE FROM inserted WITH (NOLOCK))

DECLARE @USER_ID varchar(10)
SET @USER_ID = (SELECT CR073_UPD_USER_ID FROM inserted WITH (NOLOCK))

DECLARE @CURRENT_CARD_NUM varchar(10)
SET @CURRENT_CARD_NUM = (SELECT CR073_ISSUE_LOG_ID FROM inserted WITH (NOLOCK))

DECLARE @ORD_TOT numeric(11,2)
SET @ORD_TOT = (SELECT ER100_ACT_TOTAL FROM ER100_ACCT_ORDER WITH (NOLOCK) WHERE ER100_ORD_NBR = @ORD_NBR)

DECLARE @ORD_TAX numeric(11,2)
SET @ORD_TAX = (SELECT ER100_ACT_TAX FROM ER100_ACCT_ORDER WITH (NOLOCK) WHERE ER100_ORD_NBR = @ORD_NBR)

DECLARE @RES_TYPE varchar(6)
SET @RES_TYPE = (SELECT CR073_TXT_04 FROM inserted WITH (NOLOCK))

DECLARE @RES_CODE varchar(12)
SET @RES_CODE = (SELECT CR073_TXT_05 FROM inserted WITH (NOLOCK))

EXEC sp_Assign_Cards_Update
@ORG_CODE, @EXT_ACCT_CODE, @OCCURENCE, @ISSUE_CLASS, @ISSUE_STS,
@ISSUE_TYPE, @USER_ID, @UPDATE_FLAG, @CURRENT_CARD_NUM, @ISSUE_DESC,
@ORD_NBR, @ORD_TOT, @ORD_TAX, @RES_TYPE, @RES_CODE
END
END

---