Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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

0
lbstech
Asked:
lbstech
  • 2
  • 2
1 Solution
 
jamesguCommented:
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]
0
 
lbstechAuthor Commented:
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'.
0
 
BrandonGalderisiCommented:
You're missing an END
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]
END
GO

Open in new window

0
 
lbstechAuthor Commented:
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....
0
 
BrandonGalderisiCommented:
I think that is probably one of the most USELESS errors SQL server displays.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now