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 UPDATE
ou .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 PRECEDES
especifica 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 products
tabela 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_logs
tabela 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 UPDATE
evento da products
tabela:
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 PriceLogs
tabela:
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 à PriceLogs
tabela.
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 UserChangeLogs
tabela:
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 UPDATE
gatilho para a products
tabela. Este gatilho é ativado após o before_products_update
gatilho.
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 UPDATE
declaraçã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 PriceLogs
e UserChangeLogs
:
SELECT * FROM PriceLogs;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
SELECT * FROM UserChangeLogs;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
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 TRIGGERS
instruçã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_order
coluna da triggers
tabela do information_schema
banco 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 )
Neste tutorial, você aprendeu como criar vários gatilhos para uma tabela que possuem o mesmo evento e tempo de ação.