In my database I have a table Employee that has recursive association (an employee can be boss of other employee):
[php]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;[/php]
[php]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)[/php]
Then inserts:
[php]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 [/php]
Now I have following hierarchical relation (owner > boss > worker) among the rows in table:
[php] A
/ \
B F
/ \
c D
/ \
G E[/php]
Following is Select statement for table:
[php]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)[/php]
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:
[php]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 ;[/php]
But When I perform some deletion:
[php]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.[/php]
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.
[php]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//[/php]
- A Stored Procedure to delete employee from Employee Table.
[php]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//[/php]
Best Answer
Use a stored procedure:
[sourcecode language=”plain”]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[/sourcecode]
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. Also if you need you can check article remove MySQL table
[custom-related-posts title=”You may Also Like:” none_text=”None found” order_by=”title” order=”ASC”]
