Link to home
Start Free TrialLog in
Avatar of lbstech
lbstechFlag for United States of America

asked on

Bulk Update Trigger Syntax Error

I am trying to alter an existing trigger so that it will work for all records that are updated by a command rather that the first record.  

(See attached Snippet)

I am getting the following error if i check the syntax or try to execute it:
Msg 102, Level 15, State 1, Procedure Student_Status_Change, Line 31
Incorrect syntax near 'SStatus'.

I have tried to comment out  pieces working my way back from I.[SStatus], no matter what i comment out, the same error comes back except it  says that the error occurs with the last uncommented "word"
ALTER TRIGGER [dbo].[Student_Status_Change] ON [dbo].[Students]
FOR UPDATE
AS 
BEGIN
 
DECLARE @acct_type AS INT
DECLARE @evt_type AS INT
 
SET @evt_type = 1
SET @acct_type = 0
 
DELETE FROM Status_Changes WHERE Status_Changes.[Event_Type] = 1 AND Status_Changes.[Is_Adult] = 0 AND Status_Changes.[Account_ID] IN (SELECT D.Student_ID FROM DELETED D)
 
INSERT INTO Status_Changes
(
	Event_Type, 
	Event_Data, 
	Event_Time, 
	Account_ID, 
	Is_Adult
) 
 
SELECT 
	@evt_type, 
	'SStatus=' + LTRIM(STR(I.[SStatus])), 
	GETDATE(), 
	I.[Student_ID],
	@acct_type
FROM
	INSERTED I
INNER JOIN DELETED D ON D.[Student_ID] = I.[Student_ID] WHERE D.[SStatus] != I.[SStatus]
GO

Open in new window

Avatar of jamesgu
jamesgu

what about change

INNER JOIN DELETED D ON D.[Student_ID] = I.[Student_ID] WHERE D.[SStatus] != I.[SStatus]

to

INNER JOIN DELETED D ON D.[Student_ID] = I.[Student_ID] and D.[SStatus] != I.[SStatus]
Avatar of lbstech

ASKER

I have tried that also, the same error comes back each time:
Msg 102, Level 15, State 1, Procedure SIF_Student_Elig_Change, Line 31
Incorrect syntax near 'SStatus'.
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lbstech

ASKER

Thank you so much, My head hurts from hitting it on my desk, I have been staring at this for an hour, I can't believe I missed that.  So much for following examples....
I think that is probably one of the most USELESS errors SQL server displays.