Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.

Following is query I used to create table:

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;

And I could successfully create my table:

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 I inserted some tuple in Employee table using following query.

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 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? Can some one suggest me other method ? A suggestion would be enough but should be efficient.

EDIT: Is it possible using Nested Query?
I also asked it here.

share|improve this question
    
SEE ALSO BOTH SOLUTIONS : dba.stackexchange.com/questions/29152/… –  Grijesh Chauhan Nov 24 '12 at 5:49
add comment

1 Answer 1

up vote 1 down vote accepted

The easiest way to delete D is to do the following:

  • Have B inherit G and E, putting them on the same level as D.
  • Delete D

First, I'll load your sample data

mysql> DROP DATABASE IF EXISTS grijesh;
Query OK, 1 row affected (0.06 sec)

mysql> CREATE DATABASE grijesh;
Query OK, 1 row affected (0.00 sec)

mysql> USE grijesh
Database changed
mysql> 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;
Query OK, 0 rows affected (0.08 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.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

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)

mysql>

View all current relationships

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| D    | BOSS        | E    | WORKER      |
| D    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

With this UPDATE JOIN query

UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
SET B.MSSN = A.MSSN WHERE A.SSN = '4';

let's elevate G and E to the same level as D and show relationships

mysql> UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> SET B.MSSN = A.MSSN WHERE A.SSN = '4';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

At this point, D is nobody's boss.

Now, delete it and show relationships:

mysql> DELETE FROM `Employee` WHERE SSN = '4';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Please notice that you do not need a Trigger

share|improve this answer
    
Thanks for the elaborate reply. –  Grijesh Chauhan Nov 22 '12 at 5:56
    
can't we write nested query for this? –  Grijesh Chauhan Nov 22 '12 at 5:56
    
hi answer this also: dba.stackexchange.com/questions/29170/… –  Grijesh Chauhan Nov 23 '12 at 17:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.