Resumo : neste tutorial, você aprenderá como criar um BEFORE UPDATE
gatilho MySQL para validar dados antes que eles sejam atualizados em uma tabela.
Introdução aos gatilhos MySQL BEFORE UPDATE
BEFORE UPDATE
Os gatilhos MySQL são invocados automaticamente antes que um evento de atualização ocorra na tabela associada aos gatilhos.
Aqui está a sintaxe para criar um BEFORE UPDATE
gatilho MySQL:
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
Primeiro, especifique o nome do gatilho que deseja criar após as CREATE TRIGGER
palavras-chave.
Segundo, use BEFORE UPDATE
a cláusula para especificar o horário para invocar o gatilho.
Terceiro, especifique o nome da tabela à qual o gatilho pertence após a ON
palavra-chave.
Finalmente, especifique o corpo do gatilho que contém uma ou mais instruções.
Se você tiver mais de uma instrução no trigger_body
, precisará usar o BEGIN END
bloco. Além disso, você precisa alterar o delimitador padrão da seguinte forma:
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em um BEFORE UPDATE
gatilho, você pode atualizar os NEW
valores, mas não pode atualizá- OLD
los.
Exemplo de gatilho MySQL BEFORE UPDATE
Vejamos um exemplo de uso de um BEFORE UPDATE
gatilho.
Configurando uma tabela de exemplo
Primeiro, crie uma nova tabela chamada sales
para armazenar volumes de vendas:
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id INT AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
quantity INT NOT NULL DEFAULT 0,
fiscalYear SMALLINT NOT NULL,
fiscalMonth TINYINT NOT NULL,
CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
CHECK(fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0),
UNIQUE(product, fiscalYear, fiscalMonth),
PRIMARY KEY(id)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, insira algumas linhas na sales
tabela:
INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),
('1969 Corvair Monza', 150,2020,1),
('1970 Plymouth Hemi Cuda', 200,2020,1);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, consulte os dados da sales
tabela para verificar a inserção:
SELECT * FROM sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Criando exemplo de gatilho BEFORE UPDATE
A instrução a seguir cria um BEFORE UPDATE
gatilho na sales
tabela.
DELIMITER $$
CREATE TRIGGER before_sales_update
BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
NEW.quantity,
' cannot be 3 times greater than the current quantity ',
OLD.quantity);
IF new.quantity > old.quantity * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
END $$
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O gatilho é disparado automaticamente antes que ocorra um evento de atualização para cada linha da sales
tabela.
Se você atualizar o valor na quantity
coluna para um novo valor três vezes maior que o valor atual, o gatilho gerará um erro e interromperá a atualização.
Vamos examinar o gatilho em detalhes:
Primeiro, o nome do gatilho é before_sales_update
especificado na CREATE TRIGGER
cláusula:
CREATE TRIGGER before_sales_update
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em segundo lugar, o evento desencadeador é:
BEFORE UPDATE
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, a tabela à qual o gatilho está associado é sales
:
ON sales FOR EACH ROW
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, declare uma variável e defina seu valor como uma mensagem de erro. Observe que, no BEFORE TRIGGER
, você pode acessar os valores antigos e novos das colunas por meio de modificadores OLD
e NEW
.
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
NEW.quantity,
' cannot be 3 times greater than the current quantity ',
OLD.quantity);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que usamos a CONCAT()
função para formar a mensagem de erro.
Por fim, use a IF-THEN
instrução para verificar se o novo valor é 3 vezes maior que o valor antigo e, em seguida, gere um erro usando a SIGNAL
instrução:
IF new.quantity > old.quantity * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Testando o gatilho MySQL BEFORE UPDATE
Primeiro, atualize a quantidade da linha com id 1 para 150:
UPDATE sales
SET quantity = 150
WHERE id = 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funcionou porque a nova quantidade não infringiu a regra.
Segundo, consulte os dados da sales
tabela para verificar a atualização:
SELECT * FROM sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, atualize a quantidade da linha com id 1 para 500:
UPDATE sales
SET quantity = 500
WHERE id = 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
MySQL emitiu este erro:
Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesse caso, o acionador constatou que a nova quantidade causou violação e gerou erro.
Finalmente, use o SHOW ERRORS
para exibir o erro:
SHOW ERRORS;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu como criar um BEFORE UPDATE
gatilho MySQL para validar dados antes de serem atualizados em uma tabela.