如何在 MYSQL 中删除时更新同一个表?

2022-01-01 00:00:00 sql mysql triggers mysql-error-1442

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

如果不存在则创建表`employee`(`SSN` varchar(64) 不为空，`name` varchar(64) 默认为空，`designation` varchar(128) 不为空，`MSSN` varchar(64) 默认为空，主键(`ssn`)，约束`fk_manager_employee`外键(`mssn`)引用employee(ssn)) 引擎=innodb 默认字符集=latin1;

mysql>描述员工；+--------------+--------------+------+-------+----------+-------+|领域 |类型 |空 |钥匙 |默认 |额外 |+--------------+--------------+------+-------+----------+-------+|社会保障号 |varchar(64) |否 |PRI |空 |||姓名 |varchar(64) |是 ||空 |||指定 |varchar(128) |否 ||空 |||MSSN |varchar(64) |是 |多 |空 ||+--------------+--------------+------+-------+----------+-------+4 行(0.00 秒)

然后插入:

mysql>插入员工值->(1"，A"，所有者"，NULL)，->("2", "B", "BOSS", "1"),->(3"，C"，工人"，2")，->("4", "D", "BOSS", "2"),->(5"，E"，工人"，4")，->(6"，F"，工人"，1")，->(7"、G"、工人"、4")->;查询正常，7 行受影响(0.02 秒)记录:7 重复:0 警告:0

现在我在表中的行之间有以下层次关系(所有者>老板>工人):

 A/乙/C D/通用电气

以下是表的Select语句:

mysql>选择 * 从员工；+-----+------+--------------+------+|社会保障号 |姓名 |指定 |MSSN |+-----+------+--------------+------+|1 |一个 |业主 |空 ||2 |乙 |老板 |1 ||3 |C |工人 |2 ||4 |D |老板 |2 ||5 |E |工人 |4 ||6 |F |工人 |1 ||7 |G |工人 |4 |+-----+------+--------------+------+7 行(0.00 秒)

现在，我想施加一个约束，例如:如果任何员工(BOSS)被删除，那么他手下的新BOSS将成为被删除员工(Old BOSS)的直接BOSS.例如如果我删除D 那么B 就成为G 和E 的BOSS.

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

mysql>分隔符 $$mysql>创建->删除前触发`Employee_before_delete`->开启`员工`->对于每一行开始->更新员工->SET MSSN=old.MSSN->其中 MSSN=old.MSSN;->完$$查询正常，0 行受影响(0.07 秒)mysql>分隔符；

但是当我执行一些删除操作时:

mysql>从员工那里删除 SSN='4';错误 1442 (HY000):无法更新存储函数/触发器中的表员工"因为它已经被调用这个存储的语句使用了功能/触发器.

我 在这里学习 这个触发器是不可能的 因为在 MySQL 触发器中不能操作它们分配给的表.

是否有一些其他可能的方法来做到这一点?是否可以使用嵌套查询?有人可以建议我其他方法吗?一个建议就足够了，但应该是有效的.

编辑:
我得到了答案:而不是触发存储过程或两个连续查询是可能的.首先和秒.

我为此问题编写的解决方案如下，运行良好！:

  • 我正在为 MYSQL 版本低于 5.5 编写的辅助信号函数.
<块引用>

分隔符//

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))开始SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1');从@sql 准备 my_signal_stmt;执行 my_signal_stmt;解除分配准备 my_signal_stmt;结尾//

  • 从 Employee 表中删除员工的存储过程.
<块引用>

CREATE PROCEDURE delete_employee(IN dssn varchar(64))开始声明 empDesignation varchar(128);声明 empSsn varchar(64);声明 empMssn varchar(64);SELECT SSN, designation, MSSN INTO empSsn, empDesignation, empMssn来自员工哪里 SSN = dssn;IF (empSsn 不为空) THEN案件WHEN empDesignation = 'OWNER' THENCALL my_signal('错误:无法删除所有者！');WHEN empDesignation = 'WORKER' THEN从员工那里删除 SSN = empSsn;WHEN empDesignation = 'BOSS' THEN开始更新员工SET MSSN = empMssn其中 MSSN = empSsn;从员工那里删除 SSN = empSsn;结尾;结束案例；别的CALL my_signal('错误:不是有效行！');万一;结尾//

分隔符;

解决方案

使用 存储过程:

更新 bSET b.mssn = a.mssn来自员工 a加入员工 b 上 b.mssn = a.ssn哪里 a.ssn = @deletedBoss从员工那里删除 ssn = @deletedBoss

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

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 ;

解决方案

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.

相关文章