CODEWITHSUNDEEP
sqlsql-server
froYo
froYo

Reputation: 13

MSsql trigger for DELETE

I want the trigger to prevent deletion of an employee from the Role-table if that employee is already working on a task. If the task hasn't started, the employee should be able to be deleted from the Role-table.

Trigger:

CREATE TRIGGER trg_role_del
ON Role
FOR DELETE AS

IF EXISTS (SELECT Task.StartTask, Role.EmployeeId,
FROM Task, Role, deleted
WHERE Role.EmployeeId = deleted.EmployeeId
AND Role.EmployeeId = Task.EmployeeId
AND GETDATE() > Task.StartTask)
BEGIN
    RAISERROR ('This role is working on a task, cannot be removed', 16, 1)
ROLLBACK TRANSACTION
END;

Delete statement:

DELETE FROM Role
WHERE EmplyeeId = '003'

However the trigger above does not work. All deletes goes through, the trigger doesn't stop anything. If the SELECT-statement is run separately by itself it does select the right values from the tables, so the SELECT seems fine. I can't seem to get the code to work properly. Help would be greatly appreciated!

Upvotes: 1

Views: 152

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

I would go with an instead of delete trigger:

CREATE TRIGGER trg_role_del
ON Role
INSTEAD OF DELETE AS

DELETE r
FROM Role r
INNER JOIN deleted ON r.EmployeeId = deleted.EmployeeId
LEFT JOIN Task ON r.EmployeeId = Task.EmployeeId
               AND GETDATE() > Task.StartTask
WHERE Task.Id IS NULL -- Any non-nullable column on the task table is good here

See a live demo on rextester

Upvotes: 2

etsa
etsa

Reputation: 5060

You created an "AFTER DELETE" Trigger, while you should create a "INSTEAD OF" trigger.

Upvotes: 0

Related Questions