Curso de MariaDB / Usar procedimentos, funções armazenadas e triggers
Um procedimento armazenado é chamado explicitamente com CALL, uma função armazenada retorna um valor em uma expressão SQL e um gatilho, ou trigger, é executado automaticamente diante de uma alteração em uma tabela. Embora os três armazenem lógica SQL no servidor, diferem na chamada, nas permissões e na relação com as transações.
Escolher o objeto conforme sua função
| Objeto | Chamada | Uso habitual |
|---|---|---|
| Procedimento armazenado | CALL nome(argumentos) | Operação explícita com várias instruções |
| Função armazenada | nome(argumentos) em uma expressão | Regra reutilizável que retorna um valor |
| Trigger | Automática diante de INSERT, UPDATE ou DELETE | Ação breve por linha associada à alteração de uma tabela |
Um procedimento não é obrigado a modificar dados, e uma função armazenada não está universalmente limitada a cálculos aritméticos. O exemplo atribui tarefas concretas: o procedimento altera o estoque, a função calcula um valor e o trigger registra os estoques anterior e novo. No MariaDB, o trigger atua por linha; uma instrução que altera muitas linhas também gera o trabalho correspondente.
Compare o controle de COMMIT e ROLLBACK pela conexão que chama com o contexto de segurança das views. A lógica automática pode dificultar o diagnóstico: diferencie a operação que a aplicação chama deliberadamente daquela que acompanha cada gravação correspondente. Decida quais regras pertencem à validação da aplicação e quais precisam valer para diferentes caminhos de gravação.
Preparar permissões e duas tabelas InnoDB
Use appops_lab com uma conta de estudos que tenha CREATE ROUTINE, TRIGGER e as permissões de tabela necessárias. Chamar as rotinas exige EXECUTE. Consulte a prática de contas e concessões em vez de conceder administração global à aplicação. A política de log binário pode acrescentar requisitos para criar funções; solicite sua revisão em vez de enfraquecer opções globais de confiança para executar o exemplo.
Confira o servidor e os bancos e objetos existentes antes da preparação. Se appops_lab contiver outras práticas, preserve-as. Verifique se as duas tabelas e os nomes das rotinas e do trigger abaixo estão disponíveis. A preparação não remove objetos existentes.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
product_id INT NOT NULL PRIMARY KEY,
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
old_stock INT NOT NULL,
new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);
As duas tabelas usam InnoDB. Isso permite cancelar juntos a alteração de estoque e a inserção do registro feita pelo trigger. Se algum nome já existir, examine sua definição e seus dados antes de decidir como continuar.
Criar as três definições completas
O script usa a sintaxe do cliente de linha de comando mariadb. DELIMITER indica ao cliente como reconhecer o fim de uma instrução; não é uma instrução SQL do servidor. Em um editor gráfico, use a execução de scripts da ferramenta. Com um driver Python, envie cada CREATE completo sem as linhas DELIMITER e sem os terminadores //.
DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
END IF;
RETURN ROUND(p_price * p_qty, 2);
END//
CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
IF p_delta IS NULL OR p_delta = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
END IF;
UPDATE routine_stock SET stock = stock + p_delta
WHERE product_id = p_product_id AND stock + p_delta >= 0;
IF ROW_COUNT() <> 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
END IF;
END//
CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
IF OLD.stock <> NEW.stock THEN
INSERT INTO routine_audit (product_id, old_stock, new_stock)
VALUES (NEW.product_id, OLD.stock, NEW.stock);
END IF;
END//
DELIMITER ;
line_total rejeita preços ou quantidades negativos ou NULL e retorna um valor com duas casas decimais. A quantidade zero é aceita por essa função. Confira o intervalo DECIMAL para quantidades ou preços grandes. DETERMINISTIC NO SQL descreve esta implementação e precisa continuar verdadeiro se ela for alterada. A referência oficial de CREATE FUNCTION explica suas características e segurança.
adjust_stock modifica um único produto existente se o resultado não for negativo; caso contrário, gera um erro de negócio com SIGNAL. Ele confere ROW_COUNT imediatamente depois de UPDATE, pois outra instrução pode mudar a operação à qual a contagem se refere. A referência oficial de CREATE PROCEDURE explica os parâmetros e o contexto da chamada.
O trigger insere um registro somente se o estoque mudar. OLD e NEW representam os valores anterior e novo da linha. A tabela registra mudanças como exercício; não constitui sozinha um sistema de auditoria independente e imune a adulterações. A referência oficial de CREATE TRIGGER documenta esses valores e o momento AFTER UPDATE.
Comparar as duas tabelas ao desfazer e confirmar
SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;
O valor calculado é 37.50. Dentro da transação, o estoque passa de oito para cinco e o registro mostra 8 → 5. Depois de ROLLBACK, o estoque deve voltar a oito e a quantidade de registros de auditoria, a zero. O identificador autoincremental não precisa ser reutilizado: uma lacuna na numeração não demonstra falha no cancelamento.
START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;
Depois de COMMIT, o estoque é seis e existe um registro 8 → 6. O procedimento não contém COMMIT interno, permitindo que a conexão que o chama combine a operação com outras. O trigger compartilha o contexto transacional da instrução que o ativa e não confirma separadamente.
Observar entradas inválidas e limites das permissões
Execute estas instruções separadamente. A primeira tenta retirar mais que as seis unidades atuais, e a segunda usa preço negativo. Ambas devem gerar SQLSTATE 45000. Depois, confira se o estoque continua em seis e a tabela mantém seu único registro confirmado.
CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);
A função e o procedimento declaram SQL SECURITY INVOKER, portanto os acessos às tabelas são verificados com as permissões de quem chama. Conceder EXECUTE sem UPDATE não permite alterar o estoque por esse procedimento. Um trigger usa, por sua vez, o contexto do DEFINER, a conta definidora, e não é declarado INVOKER. Gerencie essa conta, sua conservação e as permissões necessárias.
As limitações oficiais dos triggers restringem ações como controlar transações e voltar a modificar a tabela que dispara a execução. Mantenha o trabalho breve e ligado aos dados da transação, sem ocultar operações externas longas por trás de uma alteração de linha.
Examinar definições e remover somente os objetos de estudo
SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;
Guarde as definições e os contextos de segurança e confira se o backup e a restauração incluem as rotinas e os triggers necessários. Uma definição restaurada em outro servidor pode falhar se a conta DEFINER ou suas permissões estiverem ausentes. Ao concluir este exercício isolado, remova apenas seus próprios objetos:
DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;
Um ROLLBACK comum não recupera os objetos removidos. Confirme que os nomes correspondam a objetos descartáveis de estudo e preserve as outras tabelas de appops_lab. Alterar rotinas de uma aplicação também exige coordenar seus consumidores e dispor de um procedimento de implantação e recuperação.




