Crie vários gatilhos – Acervo Lima Tutorials

Resumo : neste tutorial você aprenderá como criar vários triggers para uma tabela que possuem o mesmo evento e tempo de ação.

Este tutorial é relevante para o MySQL versão 5.7.2+. Se você tiver uma versão mais antiga do MySQL, as instruções do tutorial não funcionarão.

Antes do MySQL versão 5.7.2, você só podia criar um gatilho para um evento em uma tabela, por exemplo, você só podia criar um gatilho para o evento BEFORE UPDATEou .AFTER UPDATE

O MySQL 5.7.2+ eliminou essa limitação e permitiu criar vários gatilhos para uma determinada tabela que possuem o mesmo evento e tempo de ação. Esses gatilhos serão ativados sequencialmente quando ocorrer um evento.

Aqui está a sintaxe para definir um gatilho que será ativado antes ou depois de um gatilho existente em resposta ao mesmo evento e tempo de ação:

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE} 
ON table_name FOR EACH ROW 
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
    -- statements
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, FOLLOWS ou PRECEDESespecifica se o novo gatilho deve ser invocado antes ou depois de um gatilho existente.

Permite FOLLOWS que o novo gatilho seja ativado após um gatilho existente.
Permite PRECEDES que o novo gatilho seja ativado antes de um gatilho existente.

Exemplo de múltiplos gatilhos MySQL

Usaremos a productstabela do banco de dados de exemplo para a demonstração.

Suponha que você queira alterar o preço de um produto (coluna MSRP) e registrar o preço antigo em uma tabela separada chamada PriceLogs.

Primeiro, crie uma nova price_logstabela usando a seguinte CREATE TABLE instrução:

CREATE TABLE PriceLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    updated_at TIMESTAMP NOT NULL 
			DEFAULT CURRENT_TIMESTAMP 
            ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, crie um novo gatilho que seja ativado quando ocorrer o BEFORE UPDATEevento da productstabela:

DELIMITER $$

CREATE TRIGGER before_products_update 
   BEFORE UPDATE ON products 
   FOR EACH ROW 
BEGIN
     IF OLD.msrp <> NEW.msrp THEN
         INSERT INTO PriceLOgs(productCode,price)
         VALUES(old.productCode,old.msrp);
     END IF;
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, verifique o preço do produto S12_1099:

SELECT 
    productCode, 
    msrp 
FROM 
    products
WHERE 
    productCode = 'S12_1099';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, altere o preço de um produto usando a seguinte UPDATE declaração:

UPDATE products
SET msrp = 200
WHERE productCode = 'S12_1099';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Quarto, consulte os dados da PriceLogstabela:

SELECT * FROM PriceLogs;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Funciona conforme o esperado.

Suponha que você queira registrar o usuário que alterou o preço. Para conseguir isso, você pode adicionar uma coluna adicional à PriceLogstabela.

Porém, para fins de demonstração de múltiplos gatilhos, criaremos uma nova tabela separada para armazenar os dados dos usuários que fizeram as alterações.

Quinto, crie a UserChangeLogstabela:

CREATE TABLE UserChangeLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) DEFAULT NULL,
    updatedAt TIMESTAMP NOT NULL 
	DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP,
    updatedBy VARCHAR(30) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Sexto, crie um BEFORE UPDATEgatilho para a productstabela. Este gatilho é ativado após o before_products_updategatilho.

DELIMITER $$

CREATE TRIGGER before_products_update_log_user
   BEFORE UPDATE ON products 
   FOR EACH ROW 
   FOLLOWS before_products_update
BEGIN
    IF OLD.msrp <> NEW.msrp THEN
	INSERT INTO 
            UserChangeLogs(productCode,updatedBy)
        VALUES
            (OLD.productCode,USER());
    END IF;
END$$

DELIMITER ;Linguagem de código:  HTML, XML  ( xml )

Vamos fazer um teste rápido.

Sétimo, atualize o preço de um produto usando a seguinte UPDATEdeclaração:

UPDATE 
    products
SET 
    msrp = 220
WHERE 
    productCode = 'S12_1099';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Oitavo, consulte dados de tabelas PriceLogse UserChangeLogs:

SELECT * FROM PriceLogs;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Registro de preços de gatilho múltiplo MySQL 2

SELECT * FROM UserChangeLogs;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Log de usuário de gatilho múltiplo MySQL

Como você pode ver, ambos os gatilhos foram ativados na sequência conforme esperado.

Informações sobre ordem de acionamento

Se você usar a SHOW TRIGGERSinstrução para mostrar os gatilhos, não verá a ordem que os gatilhos são ativados para o mesmo evento e tempo de ação.

SHOW TRIGGERS 
FROM classicmodels
WHERE `table` = 'products';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para encontrar essas informações, você precisa consultar a action_ordercoluna da triggerstabela do information_schemabanco de dados da seguinte forma:

SELECT 
    trigger_name, 
    action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'classicmodels'
ORDER BY 
    event_object_table , 
    action_timing , 
    event_manipulation;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )