How to Update same table on deletion in MYSQL?

Question

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 ;

Can you try the following? It looks rather too simple for your requirement specially in terms of having a dynamic deletion... `update Employee` `set MSSN=(select mssn from employee` `where ssn = '4');` `delete from employee where ssn = 4;` — bonCodigo, Nov 21 '12 at 05:53
@bonCodigo: You mean two consecutive query for deletion. May be considered...But I have to think about this -Thanks! — Grijesh Chauhan, Nov 21 '12 at 06:00
Yeah two consective queries. It also seems that EntityFramework in sql server provides Repository Pattern - it could allow you to make the repository's delete function recursive via a stored procedure. I haven't done it myself personally. Perhaps you could mimic the same here. Interesting question +1 — bonCodigo, Nov 21 '12 at 06:23
Also See : [**BOTH SOLUTIONS**](http://stackoverflow.com/questions/13518890/which-update-is-faster-using-join-or-sequential) — Grijesh Chauhan, Nov 25 '12 at 06:00

score 5 · Accepted Answer · edited Nov 22 '12 at 00:56

5

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.

edited Nov 22 '12 at 00:56

OMG Ponies

325,700
82
523
502

answered Nov 21 '12 at 05:49

Kneel-Before-ZOD

4,141
1
24
26

You mean Instead of Trigger, I should call Procedure that delete and update both...I would try.-thanks – Grijesh Chauhan Nov 21 '12 at 06:05
Yes, you can run as many statements as you want within a stored procedure. You can perform any CRUD statement on any tables all within a single stored procedure. And if the answer does help, do upvote it. Thanks. – Kneel-Before-ZOD Nov 21 '12 at 06:13
@OMGPonies: Thanks! ...Add some contact in your profile..I was trying to contact you. – Grijesh Chauhan Nov 22 '12 at 05:32
@It's Your Fault : Thanks to you too! – Grijesh Chauhan Nov 22 '12 at 05:33
@OMGPonies Hi, It seems you are of-line.. I need you help on this question: [MySQL trigger On Insert/Update events](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events) I tried to answer and I guess it should work but it doesn't works. Can you add an answer there. – Grijesh Chauhan Apr 12 '13 at 18:03
@Kneel-Before-ZOD, Isn't it better to do an update first instead of delete? (e.g. if it's foreign key bounded) – Pacerier Jan 29 '15 at 00:33

score 0 · Answer 2 · answered Nov 21 '12 at 05:50

Instead of doing a hard delete, do a soft delete. Add is_deleted column and set it to 1 if you want to delete the row. Create after_update trigger on the 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$$

I am not sure that you can update the same table for after_{insert,delete,update}. Please check this

This is a suggestion, not an answer. – Pacerier Jan 29 '15 at 00:33 — Pacerier, Jan 29 '15 at 00:33

How to Update same table on deletion in MYSQL?

2 Answers2

Linked