I created a trigger and need to assign values to multiple variabes. I am getting errors when I execute the statement.
I get the following error
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 19
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 26
Incorrect syntax near the keyword 'from'.
What am I doing wrong here?
--SCRIPT
CREATE TRIGGER Update_Payments
ON Sales AFTER INSERT
AS
BEGIN
DECLARE @CurrentOrderID int
DECLARE @CurrentPublisherID [char](10)
DECLARE @CurrentISBN [char](20)
DECLARE @CurrentContactID [char](10)
DECLARE @CurrentDate datetime
DECLARE @RoyaltyAmount money
SET NOCOUNT ON;
SET @CurrentOrderID = Select MAX(OrderID) from Sales
Select @CurrentPublisherID = P.PubisherID,
@CurrentISBN = S.ISBN,
@CurrentContactID = R.ContactID,
@RoyaltyAmount = S.SaleAmount * R.rate/100,
@CurrentDate = S.Date,
from Publishers P,Sales S,Royalty R where SOrderID = @CurrentOrderID
and S.ISBN = Publishers.ISBN and R.ISBN = S.ISBN
Insert into payments Values(@CurrentPublisherID,@CurrentISBN,@CurrentContactID,@RoyaltyAmount,@CurrentDate,'Scheduled')
SET NOCOUNT OFF;
END
GO
SET @CurrentOrderID = Select MAX(OrderID) from Sales
try
SET @CurrentOrderID = (Select MAX(OrderID) from Sales)
or simply
Select @CurrentOrderID = MAX(OrderID) from Sales