lbstech
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"
(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
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'.
Msg 102, Level 15, State 1, Procedure SIF_Student_Elig_Change, Line 31
Incorrect syntax near 'SStatus'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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]