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.