如何在删除MySQL中更新同一张表？

Question

在我的数据库中，我有一个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)

然后插入：

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

现在我在表中的行之间具有以下层次关系（所有者>老板>工人）：

     A
    / \
   B   F
  / \
 c   D
    / \
   G   E

以下是表的Select语句：

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)

现在，我想强加约束，如：If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS)。例如，如果我删除，D则B成为G和的老板E。

为此，我还编写了一个 触发器 ，如下所示：

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 ;

但是当我执行一些删除时：

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.

我在这里学到这是this trigger is not possible因为In MySQL triggers can't manipulate the table they are assigned to。

还有 其他可行的方法吗？ 有可能使用Nested Query吗？有人可以建议我其他方法吗？一个建议就足够了，但应该是有效的。

编辑 ：
我得到的答案：而不是触发一个stored procedure或two consecutive queries可能。
第一和第二。

我为这个问题写的解决方案如下， 工作正常！ ：

• 我正在写的一个辅助信号功能MYSQL version older then 5.5。

分隔符//

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//

• 从Employee表中删除员工的存储过程。

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//

定界符;

Answer 1

使用存储过程：

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

使用存储过程，您可以简单地删除所需的行，然后更新同一张表。那应该防止错误消息。