如何在 MySQL 中編寫可以從觸發器和儲存函式呼叫的信號函式？

在我的數據庫中，我有一個表：Employee通過遞歸關聯，員工可以成為其他員工的老闆。

表說明：

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

員工表的目前狀態是：

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)

以下是表中各行之間的層次關係：

    A
   / \
  B   F
 / \
c   D
   / \
  G   E

我想對 INSERT 施加以下約束：

• 員工不能對自己進行 BOSS。因此查詢之類的。

INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); 應該拒絕。

• OWNER可以插入 新的新的。

因為我正在使用早於 5.5 的 MYSQL 版本（不支持信號）。

所以我使用 a**my_signal()**作為儲存過程。

寫成這樣：

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

並應用約束，我written a Trigger知道了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 ;  

它已成功編譯並載入到數據庫中。但是當我嘗試插入時：

mysql> INSERT INTO Employee VALUES ("12", "K", "BOSS",   "12");
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

我在 這里和這裡學習

• SQL 準備語句（PREPARE、EXECUTE、DEALLOCATE PREPARE）可用於儲存過程，但不能用於儲存函式或觸發器。因此，儲存函式和触發器不能使用動態 SQL（您將語句構造為字元串然後執行它們）。

經過一番努力，我可以編寫另一個觸發器，如下所示。按照我的要求工作正常。

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

但是我已經my_signal() 在許多程序中使用，我需要編寫許多新的儲存函式和触發器，我需要my_signal()再次使用函式。

有人可以建議我以其他方式編寫 my_signal() 來列印自定義錯誤消息嗎？

我嘗試如下：

分隔符 $$

創建過程my_signal（in_errortext VARCHAR(255)）

聲明 sql varchar(512);

開始

SET sql=CONCAT(‘UPDATE ', in_errortext, 'SET x=1’);

更新 sql 設置 x =1;

結束$$

但沒用:(。

請幫助我。我將非常感激！

我特別不擅長MYSQL @Stored Procedures。

如果您想在此處嘗試您的系統，您可以快速找到建構此數據庫的命令。

您介紹的基本上是在 MySQL 5.1 中中斷觸發器的正確方法

我之前寫過這個

• Dec 23, 2011:檢查約束不起作用？
• Apr 25, 2011:在 MySQL 中觸發以防止插入

您不能在觸發器中執行動態 SQL。

您可能不得不求助於這樣的編碼my_signal：

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

然後將觸發器更改為如下所示：

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 ;  

對不起，笨拙的程式，但信號處理是 MySQL 5.1 太可怕了。

引用自：https://dba.stackexchange.com/questions/29239