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.

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

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. Hence query like.
    INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); Should be declined.
  • New new OWNER can be inserted.

As I am working with MYSQL version previous than 5.5 (doesn't supports signal).
So I am using a my_signal() as 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

I Learn here and here that

  • 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 can 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

But 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.

Can someone suggest me other way to write my_signal() through which I can print customized error message?

I tried as follows:

DELIMITER $$
CREATE PROCEDURE my_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.

share|improve this question
2  
Do I understand correctly that you have a table named "Error: can not insert new OWNER !" ? –  ypercube Nov 25 '12 at 9:10
    
@ypercube I think that's precisely what he intended. I explained why so in my answer –  RolandoMySQLDBA Nov 25 '12 at 21:26
    
@Rolando: Oh my, yes I now remember reading those answers of yours. –  ypercube Nov 25 '12 at 21:30
add comment

1 Answer

up vote 2 down vote accepted

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END// 

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;  

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

share|improve this answer
    
@RolandMySQLDBA : Thanks! Even I thought same yesterday ... Instead I made mixed my_signal() in with conditional clause in last IF clause I added PREPARE EXECUTE DEALLOCATE lines and I guess it would work because MySQL is interpreter ~~ But not worked! –  Grijesh Chauhan Nov 26 '12 at 7:07
    
Can't we include DYNAMIC SQL statements in my_signal(), Being sure that it will not executed from triggers. I think it should be allowed because its MySQL interpreter not a compiler...I did same but still it gives same Error: Dynamic SQL is not allowed in stored function or trigger Why its so? –  Grijesh Chauhan Nov 26 '12 at 16:38
    
Back on March 25, 2011, I wrote about why Dynamic SQL was restricted from Trigger use : dba.stackexchange.com/a/1906/877 –  RolandoMySQLDBA Nov 26 '12 at 16:43
1  
If you summon my_signal from a trigger, and my_signal calls for Dynamic SQL, then the trigger would be guilty by association in that the trigger calls my_signal. The trigger restrictions would be in force. –  RolandoMySQLDBA Nov 26 '12 at 16:50
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.