Trigger Constraint

Transact-SQL
1

Hi,

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]
ON [dbo].[PROJECT]
AFTER INSERT
AS

IF EXISTS (SELECT employee.STAFFNUM
FROM PROJECT right outer join EMPLOYEE
on employee.staffnum = PROJECT.staffnum
where PROJECT.lofficenum = employee.officenum)

BEGIN

SET NOCOUNT ON;

RAISERROR ('AN EMPLOYEE MUST WORK AT THE SAME OFFICE THE PROJECT RELATES TO', 16, 1)
ROLLBACK TRANSACTION
RETURN

END

The above does not work; where am I going wrong?

Thanks
Chris

2

Sounds like you want to check existence of rows like this:

IF EXISTS
(
	SELECT * FROM INSERTED i
	INNER JOIN Employee e ON 
		e.staffnum = i.staffnum 
		AND e.officenum <> i.officenum
)

The INSERTED table has the rows that were inserted or updated via the insert/update statement that caused the trigger to fire. That would be more efficient than checking the entire table.

Also, you might want to fire the trigger for updates as well.

....
ON [dbo].[PROJECT]
AFTER INSERT,UPDATE
AS
...
1 Like
3

James, this worked perfectly. I can't believe I missed this solution. Thank you very much for your help and advise.