Как обновить ту же таблицу при удалении в MYSQL?

В моей базе данных у меня есть таблица 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

Теперь у меня есть следующие иерархические отношения (owner > boss > worker) среди строк в таблице:

     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 Станьте BOSS G и E.

Для этого я также написал Trigger следующим образом:

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.

I узнайте здесь, что this trigger is not possible, потому что In MySQL triggers can't manipulate the table they are assigned to.

Можно ли использовать другой способ? Возможно ли использование Nested Query? Может ли кто-нибудь предложить мне другой метод? Предложение было бы достаточно, но должно быть эффективным.

ИЗМЕНИТЬ:
Я получил ответы: Вместо триггера возможно использование stored procedure или two consecutive queries. Первый и второй.

Решение, которое я написал для этой проблемы, как показано ниже, Working Well!:

A - вспомогательная сигнальная функция, которую я пишу для 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//

Сохраненная процедура удаления сотрудника из таблицы 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//

DELIMITER;

Ответ 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

С помощью хранимой процедуры вы можете просто удалить нужные строки, а затем обновить одну и ту же таблицу. Это должно предотвратить сообщение об ошибке.

Ответ 2

Вместо выполнения жесткого удаления сделайте мягкое удаление. Добавьте столбец is_deleted и установите его в 1, если вы хотите удалить строку. Создайте триггер after_update в таблице.

DELIMITER $$
    CREATE
    TRIGGER `Employee_after_update` AFTER UPDATE
      ON `Employee`
      FOR EACH ROW BEGIN
      UPDATE Employee
      ...............
      WHERE NEW.is_deleted=1; 
      DELETE FROM Employee WHERE NEW.is_deleted=1
    END$$

Я не уверен, что вы можете обновить ту же таблицу для after_ {insert, delete, update}. Пожалуйста, проверьте это