Início / dba / Perguntas / 29239
Accepted
Grijesh Chauhan
Asked: 2012-11-25 22:06:53 +0800 CST

Como escrever a função de sinal no MySQL que pode ser chamada de Triggers e Stored Functions?

  • 772

No meu banco de dados eu tenho uma tabela : Employeecom associação recursiva, um funcionário pode ser chefe de outro funcionário.

A descrição da tabela: 

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

Estado Atual da Tabela de Empregados é: 

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)

Segue a relação hierárquica entre as linhas da tabela: 

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

Eu queria impor as seguintes restrições ao INSERT :

  • Um empregado não pode mandar em si mesmo. Daí a consulta como.
    INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); Deve ser recusado.
  • Novo novo OWNERpode ser inserido.

Como estou trabalhando com MYSQL versão anterior a 5.5 (não suporta sinal).
Portanto, estou usando a my_signal()como um procedimento armazenado.

Escrito assim: 

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

E para aplicar restrições , written a Triggercomo vim a saber disso 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 ;

Foi compilado e carregado com sucesso no banco de dados. Mas quando tentei inserir: 

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

Aprendo aqui e aqui que

  • As instruções SQL preparadas (PREPARE, EXECUTE, DEALLOCATE PREPARE) podem ser usadas em procedimentos armazenados, mas não em funções ou gatilhos armazenados. Portanto, funções armazenadas e gatilhos não podem usar SQL dinâmico (onde você constrói instruções como strings e depois as executa).

Depois de algum esforço, posso escrever outro gatilho conforme abaixo. Funcionando bem conforme minha necessidade.

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

Mas já estou usando my_signal() em muitos procedimentos e preciso escrever muitas novas funções e gatilhos armazenados onde precisaria de my_signal()funções novamente.

Alguém pode me sugerir outra maneira de escrever my_signal () através da qual eu possa imprimir uma mensagem de erro personalizada?

Eu tentei da seguinte forma:

DELIMITADOR $$
CREATE PROCEDURE my_signal(in_errortext VARCHAR(255))
DECLARE sql varchar(512);
COMEÇAR
SET sql=CONCAT('ATUALIZAR ', in_errortext, 'CONJUNTO x=1');
ATUALIZAR sql SET x =1;
END$$

Mas inútil :( .

Por favor, me ajude nisso. Ficarei muito grato!

Não sou bom em MYSQL, especialmente @ Stored Procedures.

Se você quiser experimentar seu sistema aqui, poderá encontrar rapidamente comandos para criar esse banco de dados.

mysql trigger

1 respostas

  1. Best Answer

    O que você apresentou é essencialmente a maneira correta de interromper os gatilhos no MySQL 5.1

    Eu escrevi sobre isso antes

    Você não pode fazer SQL dinâmico em gatilhos.

    Você pode ter que recorrer à codificação da my_signalseguinte forma:

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

    em seguida, altere o gatilho para ficar assim:

    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 ;

    Desculpe pela programação desajeitada, mas o processamento de sinal do MySQL 5.1 é horrível.

    • 2

relate perguntas