How to Update same table on deletion in MYSQL?
mysql, mysql-error-1442, sql, triggers
Solution
Use a stored procedure:
UPDATE b
SET b.mssn = a.mssn
FROM EMPLOYEE a
JOIN EMPLOYEE b ON b.mssn = a.ssn
WHERE a.ssn = @deletedBoss
DELETE FROM employee WHERE ssn = @deletedBoss
With a stored procedure, you can simply delete the rows you want, and after that, update the same table. That should prevent the error message.
Problem
In my database I have a table `Employee` that has recursive association (an employee can be boss of other employee): ``` create table if not exists `employee` ( `SSN` varchar(64) not null, `name` varchar(64) default null, `designation` varchar(128) not null, `MSSN` varchar(64) default null, primary key (`ssn`), constraint `fk_manager_employee` foreign key (`mssn`) references employee(ssn) ) engine=innodb default charset=latin1; ``` ``` mysql> describe Employee; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | SSN | varchar(64) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | designation | varchar(128) | NO | | NULL | | | MSSN | varchar(64) | YES | MUL | NULL | | +-------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) ``` Then inserts: ``` mysql> insert into Employee values -> ("1", "A", "OWNER", NULL), -> ("2", "B", "BOSS", "1"), -> ("3", "C", "WORKER", "2"), -> ("4", "D", "BOSS", "2"), -> ("5", "E", "WORKER", "4"), -> ("6", "F", "WORKER", "1"), -> ("7", "G", "WORKER", "4") -> ; Query OK, 7 rows affected (0.02 sec) Records: 7 Duplicates: 0 Warnings: 0 ``` Now I have following hierarchical relation (owner > boss > worker) among the rows in table: ``` A / \ B F / \ c D / \ G E ``` Following is Select statement for table: ``` mysql> SELECT * FROM Employee; +-----+------+-------------+------+ | SSN | name | designation | MSSN | +-----+------+-------------+------+ | 1 | A | OWNER | NULL | | 2 | B | BOSS | 1 | | 3 | C | WORKER | 2 | | 4 | D | BOSS | 2 | | 5 | E | WORKER | 4 | | 6 | F | WORKER | 1 | | 7 | G | WORKER | 4 | +-----+------+-------------+------+ 7 rows in set (0.00 sec) ``` Now, I want to impose a constraint like : `If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS)`. e.g. If I delete `D` then `B` Become BOSS of `G` and `E`. For that I also written a Trigger as follows: ``` mysql> DELIMITER $$ mysql> CREATE -> TRIGGER `Employee_before_delete` BEFORE DELETE -> ON `Employee` -> FOR EACH ROW BEGIN -> UPDATE Employee -> SET MSSN=old.MSSN -> WHERE MSSN=old.MSSN; -> END$$ Query OK, 0 rows affected (0.07 sec) mysql> DELIMITER ; ``` But When I perform some deletion: ``` mysql> DELETE FROM Employee WHERE SSN='4'; ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ``` I learn here that `this trigger is not possible` because `In MySQL triggers can't manipulate the table they are assigned to`. Is there some other possible way to do this? Is it possible using `Nested Query`? Can some one suggest me other method ? A suggestion would be enough but should be efficient. EDIT: I got answers: Instead of trigger a `stored procedure` or `two consecutive queries` is possible. First and second. The Solution I wrote for this problem as below, Working Well!: - A a helper signal function as I am writing for `MYSQL version older then 5.5`. DELIMITER // ``` CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255)) BEGIN SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1'); PREPARE my_signal_stmt FROM @sql; EXECUTE my_signal_stmt; DEALLOCATE PREPARE my_signal_stmt; END// ``` - A Stored Procedure to delete employee from `Employee` Table. ``` CREATE PROCEDURE delete_employee(IN dssn varchar(64)) BEGIN DECLARE empDesignation varchar(128); DECLARE empSsn varchar(64); DECLARE empMssn varchar(64); SELECT SSN, designation, MSSN INTO empSsn, empDesignation, empMssn FROM Employee WHERE SSN = dssn; IF (empSsn IS NOT NULL) THEN CASE WHEN empDesignation = 'OWNER' THEN CALL my_signal('Error: OWNER can not deleted!'); WHEN empDesignation = 'WORKER' THEN DELETE FROM Employee WHERE SSN = empSsn; WHEN empDesignation = 'BOSS' THEN BEGIN UPDATE Employee SET MSSN = empMssn WHERE MSSN = empSsn; DELETE FROM Employee WHERE SSN = empSsn; END; END CASE; ELSE CALL my_signal('Error: Not a valid row!'); END IF; END// ``` DELIMITER ;