Início / dba / Perguntas / 11046
Accepted
Mark D
Asked: 2012-01-21 07:00:01 +0800 CST

Desativar gatilho para apenas uma tabela

  • 772

É possível desabilitar um gatilho momentaneamente, mas para apenas uma tabela.

Por exemplo, eu tenho tabela, TableA com um gatilho de inserção, atualização e exclusão. Eu também tenho uma tabela B com os mesmos gatilhos, mas eles afetam apenas certas colunas na tabela A.

Agora tenho uma consulta de atualização que usa ambas as tabelas. Eu sei que as atualizações na tabela A precisam disparar os gatilhos, mas as atualizações na tabela B definitivamente não precisam disparar os gatilhos. Então, eu gostaria de desabilitar esses gatilhos até que as atualizações sejam feitas.

Isso é possível? Estou usando o MySQL 5.1

[TERMO ADITIVO]

Aqui está uma tabela de gatilhos A essencialmente

BEGIN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN  
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
END

Aqui estão os gatilhos na tabela B essencialmente

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

Aqui está o procedimento armazenado que é disparado da tabela B

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN 

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$
mysql trigger

3 respostas

  1. Best Answer

    Na verdade, se você colocar um bloco if then em cada gatilho, poderá efetivamente desligar todos os gatilhos. Aqui está um bloco de código

    IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

    No ambiente mysql, você pode

    • correSET @TRIGGER_DISABLED = 1;
    • faça a manutenção dos seus dados
    • correSET @TRIGGER_DISABLED = 0;

    Portanto, seu gatilho para a tabela A deve ficar assim:

    BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

    Portanto, seu gatilho para a tabela B deve ficar assim:

    CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

    Se você quiser que os gatilhos da tabela A sejam iniciados, mas não da tabela B, adicione o bloco de código apenas ao gatilho da tabela B.

    • 7

  2. Você pode simplificar essa abordagem.

    1. Basta adicionar um rótulo ao primeiro bloco "BEGIN".
    2. Teste, se a variável de controle for NOT NULL.
    3. Se sim, DEIXE o gatilho.

    Assim, você pode evitar envolver o código de disparo original em uma instrução "IF". Apenas o Trigger-Head deve ser modificado de forma bem definida - o que é mais simples e muito mais confiável.

    Exemplo:

    CREATE TRIGGER [YOUR_TRIGGER_SPEC]
Trigger: BEGIN

      IF @TRIGGER_DISABLED NOT NULL THEN
         LEAVE Trigger;
      END IF;

      [YOUR CODE]
END;

    Divirta-se :-)

    Editar por RolandoMySQLDBA 2012-05-01 18:38 EDT

    Na verdade, eu tentei isso meses atrás e é instável. Aqui está como:

    Tabela de Amostra

    mysql> show create table mytext\G
*************************** 1. row ***************************
       Table: mytext
Create Table: CREATE TABLE `mytext` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` text NOT NULL,
  `txtmd5` char(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `txtmd5` (`txtmd5`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mytext;
+----+------------------------+----------------------------------+
| id | txt                    | txtmd5                           |
+----+------------------------+----------------------------------+
|  1 | Rolando Edwards        | ab14209a029a8f6d42d7c5a5d7a77623 |
|  2 | Pamela Edwards         | 5dcbd06ea48c690032b1b29a514eb0e2 |
|  3 | Dominique Edwards      | 7487431d74ac2d17a9d63123672a4bdf |
|  4 | Diamond Edwards        | bc8d80541a000ed506048134058e2878 |
|  5 | The Quick Brown Fox    | be94284cfa534c1837e744c061f71e17 |
| 11 | Quick Brown Fox Jumped | 495a52136057b242a80e514d7cbe77c7 |
+----+------------------------+----------------------------------+
6 rows in set (0.00 sec)

mysql>

    Aqui está o gatilho:

    DROP TRIGGER IF EXISTS mytext_bi;
DELIMITER $$
CREATE TRIGGER mytext_bi BEFORE INSERT ON mytext FOR EACH ROW
TheTrigger:BEGIN
    DECLARE found_count INT;
    SELECT COUNT(1) INTO found_count
    FROM mytext WHERE txtmd5 = MD5(LEFT(new.txt,10));
    IF found_count = 1 THEN
        LEAVE TheTrigger;
    END IF;
    SET new.txtmd5 = MD5(LEFT(new.txt,10));
END $$
DELIMITER ;

    Olha o que acontece onde eu tento INSERT 'Dominique Edwards'

    mysql> insert into mytext (txt) values ('Dominique Edwards');
Query OK, 1 row affected (0.06 sec)

mysql> select * from mytext;
+----+------------------------+----------------------------------+
| id | txt                    | txtmd5                           |
+----+------------------------+----------------------------------+
|  1 | Rolando Edwards        | ab14209a029a8f6d42d7c5a5d7a77623 |
|  2 | Pamela Edwards         | 5dcbd06ea48c690032b1b29a514eb0e2 |
|  3 | Dominique Edwards      | 7487431d74ac2d17a9d63123672a4bdf |
|  4 | Diamond Edwards        | bc8d80541a000ed506048134058e2878 |
|  5 | The Quick Brown Fox    | be94284cfa534c1837e744c061f71e17 |
| 11 | Quick Brown Fox Jumped | 495a52136057b242a80e514d7cbe77c7 |
| 14 | Dominique Edwards      |                                  |
+----+------------------------+----------------------------------+
7 rows in set (0.00 sec)

mysql>

    AHHH, CAIU DE QUALQUER FORMA!!!

    Ainda vou marcar sua resposta com +1 não apenas por causa do seu esforço, mas também porque tentei exatamente a mesma coisa (colocar um rótulo perto de BEGIN) e não funcionou para mim. Que maneira de aprender.

    Ninguém deve colocar plena fé na MySQL Stored Procedure Language (processamento de sinal não está implementado corretamente nela) e você enfatizou isso quando disse "Divirta-se". Às vezes, divertir-se significa experimentar e ver!!! . Esse é o verdadeiro espírito de aprendizagem.

    Contornei assim:

    DROP TRIGGER IF EXISTS mytext_bi;
DELIMITER $$
CREATE TRIGGER mytext_bi BEFORE INSERT ON mytext FOR EACH ROW
BEGIN
    DECLARE found_count INT;
    SELECT COUNT(1) INTO found_count
    FROM mytext WHERE txtmd5 = MD5(LEFT(new.txt,10));
    IF found_count = 0 THEN
        SELECT COUNT(1) INTO found_count FROM table_that_does_not_exist;
    END IF;
    SET new.txtmd5 = MD5(LEFT(new.txt,10));
END $$
DELIMITER ;

    A natureza dos procedimentos armazenados é passar pelo código completo navegando pelos blocos IF-THEN como fiz na minha resposta ou usar o processamento de sinal (novamente, não está totalmente operacional)

    Bem-vindo ao DBA StackExchange !!! Novamente, +1 como Prometido.

    Desculpe pela longa edição, mas não consegui colocar meu código de exemplo em um comentário.

    • 4

  3. antes de mais nada: obrigado pelo '+1' :-)

    E - você está certo: a desativação de um gatilho NÃO IMPEDE A AÇÃO EM SI. Não existe uma maneira "correta" de fazer esse tipo de sinal "ABORT" implementado pelo MySQL no momento.

    A "solução alternativa" comum é - como você descreveu em sua resposta - gerar uma exceção SQL. Assim, toda a ação será impedida por um efeito colateral (a exceção).

    Mas: tenha cuidado. Essa "solução" às vezes cria mais problemas - pense no gatilho transacional e nas reversões necessárias - e esperançosamente controladas. Uuuups! (É por isso que usei as aspas para essa abordagem).

    Além disso, é muito difícil depurar esses tipos de implementações. Com este comportamento, o desenvolvedor, administrador ou mantenedor deve ser capaz de distinguir entre "boas" exceções SQL e problemas realmente ocorridos. Horrível.

    E por último não menos importante: esse tipo de limitação atinge CADA rotina armazenada no MySQL (procedimentos, funções E trigger)!

    Aliás: essa falta de controle é culpa de cada provedor de um RDBMS. E sim - é isso que quero dizer com "Divirta-se" :-)

    Certo, de volta às raízes - a pergunta era: "DESATIVAR um gatilho" (não PREVENIR a ação). Em particular para este requisito especial, a abordagem é segura e confiável.

    Mais uma informação "atrás do szene": A abordagem fornecida funciona com segurança, porque o MySQL inicializa variáveis ​​baseadas em sessão como '@TRIGGER_DISABLED' com NULL - isso também é um efeito colateral.

    E lembre-se: esse tipo de variável é visível apenas para a sessão CURRENT! Assim, você pode desabilitar o gatilho para SUA sessão atual. Este tipo de implementação NÃO tem efeitos "em todo o servidor"!

    Se você precisar desse tipo de coisa (desabilitar o gatilho para todo o servidor), você DEVE usar objetos visíveis e acessíveis em todo o servidor, como variáveis ​​de servidor (uuups - outro tipo de "solução alternativa" com efeitos colaterais imprevisíveis) ou - a maneira mais limpa - tabelas.

    Então, também "desculpe" pela minha longa resposta e novamente: divirta-se :-)

    • 2

relate perguntas