CREATE TRIGGER; Incorrect syntax near DECLARE

Question

Here's my code:

CREATE TRIGGER trg_Booking
ON Bookings
FOR INSERT, UPDATE

DECLARE @bef AS varchar(25)

SELECT @bef = Role
FROM Inserted, booksys.dbo.Employees
WHERE Inserted.EmployeeNo = Employees.EmployeeNo

IF NOT (@bef = 'Foreman')

BEGIN 
    RAISERROR ('Only a Foreman can do that!',16,1)
    ROLLBACK TRANSACTION
END

I keep getting an error message when trying t execute and create the trigger; "Msg 156, Level 15, State 1, Procedure trg_booking, Line 5 Incorrect syntax near the keyword 'DECLARE'."

Also, it seems, SQL Server does not recognize the object "Inserted"?

I'm using SQL Server 2014 and Management Studio.

Answer 1

You were missing As after FOR INSERT, UPDATE

if you have more than one statement inside trigger always use Begin and END

CREATE TRIGGER booksys.dbo.trg_Booking
ON booksys.dbo.Bookings
FOR INSERT, UPDATE
as
Begin

IF Not exists ( SELECT 1 
FROM Inserted Inner Join booksys.dbo.Employees
on Inserted.EmployeeNo = Employees.EmployeeNo
where Role='Foreman')

BEGIN 
    RAISERROR ('Only a Foreman can do that!',16,1)
    ROLLBACK TRANSACTION
END
END