I am relatively new to SQL and have encountered a problem i was hoping someone could help with. I have 3 tables: Office, Employee and Project.
Each office can have zero one or many projects and each project can be associated with exactly one office.
Each office can have one or many employees and each employee is employed by exactly one office
Each project is managed by exactly one Employee and each employee can manage many projects.
I am trying to create a Trigger which should fire when a row is entered into the Project table. Only those staff members employed by the same Office as the Project relates to, can manage that project: i.e. Projects owned by the New York office are manage by employees in the New York Office, Projects in the London Office are managed by employee in the London office etc.
This is what i have so far:
PK enclosed in stars.
Project Table columns: Office number, Project Number, Staff Num, Project Name
Office table: Office Number office name
EMployee table: Staff Num name.
CREATE TRIGGER [dbo].[EMPLOYEE_MUST_WORK_IN_SAME_OFFICE_AS_PROJECT_RELATES]
IF EXISTS (SELECT employee.STAFFNUM
FROM PROJECT right outer join EMPLOYEE
on employee.staffnum = PROJECT.staffnum
where PROJECT.lofficenum = employee.officenum)
SET NOCOUNT ON;
RAISERROR ('AN EMPLOYEE MUST WORK AT THE SAME OFFICE THE PROJECT RELATES TO', 16, 1)
The above does not work; where am I going wrong?