In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC 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) | NO | MUL | NULL | |
+-------------+--------------+------+-----+---------+-------+
Present State of Employee Table is:
mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1 | A | OWNER | 1 |
| 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)
Following is hierarchical relation among the rows in table:
A
/ \
B F
/ \
c D
/ \
G E
I wanted to impose following constraints on INSERT:
- An employee can't BOSS of himself. This is allowed for OWNER only. Hence query like.
INSERT INTO Employee VALUES ("8", "H", "BOSS", "8");Should be declined. - New new
OWNERcan be inserted.
As I am working with MYSQL version previous than 5.5 (doesn't support signal).
So I am using a my_singal() a stored procedure.
Written Like this:
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//
And to Apply constraints I written a Trigger as I came to know that check constraints are not yet implemented in MySQL!
DELIMITER $$ CREATE TRIGGER `employee_before_insert` BEFORE INSERT ON `Employee` FOR EACH ROW BEGIN CASE WHEN NEW.designation = 'OWNER' THEN CALL my_signal('Error: can not insert new OWNER !'); WHEN NEW.SSN = NEW.MSSN THEN CALL my_signal('Error: Row can not reference itself!'); END CASE; END$$ DELIMITER ;
It was successfully compiled and loaded in database. But when I tried to insert:
mysql> INSERT INTO Employee VALUES ("12", "K", "BOSS", "12");
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
- SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them).
After some effort I could write an another trigger as below. Working fine as per my requirement.
mysql> CREATE -> TRIGGER `employee_before_insert` BEFORE INSERT -> ON `Employee` -> FOR EACH ROW BEGIN -> IF UCASE(NEW.designation) = 'OWNER' THEN /*UCASE*/ -> UPDATE `Error: can not insert new OWNER !` set x=1; -> END IF; -> IF (NEW.SSN = NEW.MSSN) THEN -> UPDATE `Error: Row can not reference itself!` set x=1; -> END IF; -> END$$ Query OK, 0 rows affected (0.08 sec) mysql> DELIMITER ; mysql> INSERT INTO Employee VALUES ("12", "K", 'owner', "11"); ERROR 1146 (42S02): Table 'dumy.Error: can not insert new OWNER !' doesn't exist mysql> INSERT INTO Employee VALUES ("12", "K", 'Ajay', "12"); ERROR 1146 (42S02): Table 'dumy.Error: Row can not reference itself!' doesn't exist
Cause I am already using my_signal() in many procedures and I need to write many new Stored functions and triggers where I would need my_signal() functions again.
It would also good for easy manipulation if sometime MYSQL version upgraded to 5.5(+).
Can someone suggest me other way to write my_signal() through which I can print customized error message?
I tried as follows:
DELIMITER $$
CREATE PROCEDUREmy_signal(in_errortext VARCHAR(255))
DECLARE sql varchar(512);
BEGIN
SET sql=CONCAT('UPDATE', in_errortext, 'SET x=1');
UPDATE sql SET x =1;
END$$
But useless :( .
Please help me on this.I will be very thankful!
I am not good at MYSQL specially @ Stored Procedures.
If you would like to try on your system here you can quickly find commands to build this database.
EDIT ~ MySQL interpreter behaving as compiler!
We can't call Dynamic SQL statements from Triggers and Stored Functions.
To keep same interface my_signal() in all stored routine I modified my_signal(); Added Static SQL statements with Dynamic SQL and being sure that it will not executed from triggers. I thought it should be allowed because MySQL is an interpreter not a compiler.
New my_signal()
DELIMITER $$
CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
IF in_errortext = 'ERROR_INSERT_OWNER' THEN /* Static SQL*/
UPDATE `Error: can not insert new OWNER !` set x=1;
END IF;
IF in_errortext = 'ERROR_INSERT_SELF_REFERENCE' THEN /* Static SQL*/
UPDATE `Error: Row can not reference itself!` set x=1;
ELSE /* Dynamic SQL*/
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 IF;
END$$
New Trigger
DELIMITER $$
CREATE
TRIGGER `employee_before_insert` BEFORE INSERT
ON `Employee`
FOR EACH ROW BEGIN
CASE
WHEN UPPER(NEW.name) = 'OWNER' THEN
CALL my_signal('ERROR_INSERT_OWNER');
WHEN NEW.SSN = NEW.MSSN THEN
CALL my_signal('ERROR_INSERT_SELF_REFERENCE');
END CASE;
END$$
DELIMITER ;
Hopefully I tried to insert:
mysql> INSERT INTO Employee VALUES ("9", "X", "WOKER", "9" );
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
mysql> INSERT INTO Employee VALUES ("9", "X", "OWNER", "9" );
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
OOPs! its behaving like compiler but not interpreters. Is it not?
EDIT: Answer
While I was editing question luckily I got answer by RolandoMySQLDBA.
ANSWER IS HERE
Last: I could understand that we cannot do Dynamic SQL in triggers. and I may have to resort to coding the my_signal(). No other option yet now in MySQL.
