Comment mettre à jour la même table sur la suppression dans MYSQL?

dans ma base de données j'ai un tableau Employee qui a une association récursive (un employé peut être le patron d'un autre employé): 

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)

puis insère: 

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

maintenant j'ai la relation hiérarchique suivante (propriétaire > patron > travailleur) parmi les lignes dans le tableau: 

     A
    / 
   B   F
  / 
 c   D
    / 
   G   E

la déclaration suivante est choisie pour la 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)

Maintenant, je veux imposer une contrainte comme : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS) . par exemple, si je supprime D , alors B devient patron de G et E .

pour cela j'ai aussi écrit un Trigger comme suit: 

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 ;

mais quand j'effectue une suppression: 

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.

je apprendre ici que this trigger is not possible parce que In MySQL triggers can't manipulate the table they are assigned to .

y a-t-il une autre façon de le faire? est-il possible d'utiliser Nested Query ? Peut quelqu'un me suggérer une autre méthode ? Une suggestion serait suffisante mais devrait être efficace.

MODIFIER :

J'ai eu des réponses: Au lieu de déclencher un stored procedure ou two consecutive queries est possible. Première et deuxième .

la Solution que j'ai écrite pour ce problème comme ci-dessous, fonctionne bien! :

  • A une aide de la fonction de signal que je suis en train d'écrire pour 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//
  • procédure entreposée pour supprimer l'employé de la Table 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//

délimiteur ;

13
demandé sur Community 2012-11-21 09:37:19

2 réponses

utiliser un procédure stockée : 

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

avec une procédure stockée, vous pouvez simplement supprimer les lignes que vous voulez, et après cela, mettre à jour la même table. Cela devrait empêcher le message d'erreur.

5
répondu Kneel-Before-ZOD 2012-11-22 00:56:18

au lieu de faire une suppression dure, faites une suppression douce. Ajoutez la colonne is_deleted et mettez-la à 1 si vous voulez supprimer la ligne. Créer le déclencheur after_update sur la table. 

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

Je ne suis pas sûr que vous puissiez mettre à jour la même table pour after_{insert,delete,update}. Veuillez cocher cette case

0
répondu Karthik Appigatla 2012-11-21 05:50:37