this link there the database which will need to work with... question is:- Write a trigger that is triggered when deleting an employee.

The trigger will also delete from the employeeeterritories table all records linked to the employee Deleted.

Hi, what is the problem?

DELIMITER // create trigger removeEmployee before delete on employees for each row begin
delete FROM employeeterritories et where et.employeeId in (select e.EmployeeID from employees e); end // DELIMITER ;

question is:- Write a trigger that is triggered when deleting an employee.

The trigger will also delete from the employeeeterritories table all records linked to the employee Deleted.

Hi, what is the problem?

DELIMITER // create trigger removeEmployee before delete on employees for each row begin
delete FROM employeeterritories et where et.employeeId in (select e.EmployeeID from employees e); end // DELIMITER ;

CodePudding user response：

Your subquery (select e.EmployeeID from employees e) will get all ID from table employees , which then makes all employees matching them get their rows wiped out. In triggers , we can use keyword OLD and NEW to present the corresponding rows in the original table. In this case, using OLD is suitable. Also , use the = operator instead of IN as MySQL trigger is working in a row by row process due to the FOR EACH ROW part .

DELIMITER // 
create trigger removeEmployee before delete on employees for each row begin
delete FROM employeeterritories  where employeeId = OLD.EmployeeID; 
end // 

CodePudding user response：

this the error i get when try to delete the employee

this the error i get when try to delete the employee:- Error Code: 1093. You can't specify target table 'employeeterritories' for update in FROM clause