如何在MYSQL中删除时更新同一张表? [英] How to Update same table on deletion in MYSQL?

问题描述

在我的数据库中，有一个表Employee具有递归关联(一个雇员可以是其他雇员的老板):

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)

然后插入:

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

以下是表的Select语句:

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)

现在，我想施加一个约束:If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS).例如如果删除D，则B成为G和E的BOSS.

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.

为此，我还编写了 Trigger ，如下所示:

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.

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

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.

编辑:
我得到了答案: 代替触发，可以使用stored procedure或two consecutive queries. 第一和

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!:

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

• A a helper signal function as I am writing for MYSQL version older then 5.5.

DELIMITER//

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

• 用于从Employee表中删除员工的存储过程.
• 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;

DELIMITER ;

推荐答案

使用存储过程:

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.

这篇关于如何在MYSQL中删除时更新同一张表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！