Como chamar um procedimento armazenado a partir de um gatilho no MySQL

Resumo : neste tutorial, você aprenderá como chamar um procedimento armazenado a partir de um gatilho no MySQL.

MySQL permite que você chame um procedimento armazenado a partir de um gatilho usando a CALLinstrução. Ao fazer isso, você pode reutilizar o mesmo procedimento armazenado em vários gatilhos.

No entanto, o gatilho não pode chamar um procedimento armazenado que tenha parâmetros OUTou INOUTou um procedimento armazenado que use SQL dinâmico.

Vamos dar uma olhada no exemplo a seguir.

Configurando uma tabela de exemplo

Primeiro, crie uma nova tabela chamada accounts:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    accountId INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    amount DECIMAL(10 , 2 ) NOT NULL ,
    PRIMARY KEY (accountId),
    CHECK(amount >= 0) 
);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, insira duas linhas na accountstabela:

INSERT INTO accounts(name, amount)
VALUES
    ('John Doe', 1000),
    ('Jane Bush', 500);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados da accountstabela.

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

Crie um procedimento armazenado que retire de uma conta

Este extrato cria um procedimento armazenado que retira uma quantia em dinheiro de uma conta:

DELIMITER $$

CREATE PROCEDURE Withdraw(
    fromAccountId INT, 
    withdrawAmount DEC(10,2)
)
BEGIN
    IF withdrawAmount <= 0 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'The withdrawal amount must be greater than zero';
    END IF;
    
    UPDATE accounts 
    SET amount = amount - withdrawAmount
    WHERE accountId = fromAccountId;
END$$

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

Neste procedimento armazenado, se o valor da retirada for menor ou igual a zero, ocorrerá um erro. Caso contrário, atualiza o valor da conta.

Crie um procedimento armazenado que verifique a retirada

A instrução a seguir cria um procedimento armazenado que verifica a retirada de uma conta:

DELIMITER $$

CREATE PROCEDURE CheckWithdrawal(
    fromAccountId INT,
    withdrawAmount DEC(10,2)
)
BEGIN
    DECLARE balance DEC(10,2);
    DECLARE withdrawableAmount DEC(10,2);
    DECLARE message VARCHAR(255);

    -- get current balance of the account
    SELECT amount 
    INTO balance
    FROM accounts
    WHERE accountId = fromAccountId;
    
    -- Set minimum balance
    SET withdrawableAmount = balance - 25;

    IF withdrawAmount > withdrawableAmount THEN
        SET message = CONCAT('Insufficient amount, the maximum withdrawable is ', withdrawableAmount);
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = message;
    END IF;
END$$

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

Neste procedimento armazenado:

• Primeiro, obtenha o saldo atual da conta.
• Em segundo lugar, defina o valor sacável. O saldo mínimo da conta deve ser 25.
• Terceiro, gere um erro se o valor retirado for maior que o valor sacável.

Crie um gatilho que chame um procedimento armazenado

A instrução a seguir cria um BEFORE UPDATEgatilho que chama o procedimento armazenado CheckWithdrawal:

DELIMITER $$

CREATE TRIGGER before_accounts_update
BEFORE UPDATE
ON accounts FOR EACH ROW
BEGIN
    CALL CheckWithdrawal (
        OLD.accountId, 
        OLD.amount - NEW.amount
    );
END$$

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

Testando as transações

Primeiro, retire 400 do ID da conta 1:

CALL withdraw(1, 400);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Funcionou.

Segundo, consulte os dados da accountstabela:

SELECT * FROM accounts
WHERE accountId = 1;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O saldo atual da conta id 1 é 600.

Terceiro, retire 600 do ID da conta 1:

CALL withdraw(1, 600);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O procedimento armazenado Withdrawexecuta uma UPDATEinstrução que invoca automaticamente o gatilho before_accounts_update.

O before_account_updategatilho então chama o procedimento armazenado CheckWithdrawalpara verificar a retirada. Emite um erro porque o valor do saque faz com que o saldo mínimo seja zero, que é menor que 25.

Error Code: 1644. Insufficient amount, the maximum withdrawable is 575.00Linguagem de código:  JavaScript  ( javascript )

Quarto, retire 575 do ID da conta 1:

CALL withdraw(1, 575);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por fim, verifique o saque consultando os dados da tabela de contas:

SELECT * 
FROM accounts
WHERE accountId = 1;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste tutorial, você aprendeu como chamar um procedimento armazenado a partir de um gatilho no MySQL.