如何在MySQL中从触发器调用存储过程

时间:2019-11-20 08:52:19  来源:igfitidea点击:

简介:在本教程中,您将学习如何从MySQL中的触发器调用存储过程。

MySQL允许您使用CALL语句从触发器中调用存储过程。
这样,您可以在多个触发器中重用同一存储过程。

但是,触发器无法调用具有OUT或INOUT参数的存储过程或使用动态SQL的存储过程。

让我们看看以下示例。

创建测试表

首先,创建一个名为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) 
);

第二,在accounts表中插入两行:

INSERT INTO accounts(name, amount)
VALUES
    ('John Doe', 1000),
    ('Jane Bush', 500);

第三,从帐户表中查询数据。

SELECT * FROM accounts;

创建一个从帐户中提取的存储过程

该语句创建一个存储过程,该过程从一个帐户中提取一定数量的钱:

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 ;

在此存储过程中,如果提款金额小于或等于零,则会引发错误。
否则,它将更新帐户金额。

创建一个存储过程,以检查提款

以下语句创建一个存储过程,该过程检查从帐户中提取的款项:

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 ;

在此存储过程中:

  • 首先,获取帐户的当前余额。

  • 其次,设定可提取金额。
    帐户的最小余额必须为25。

  • 第三,如果提款金额大于可提款金额,则会引发错误。

创建一个调用存储过程的触发器

以下语句创建一个BEFORE UPDATE触发器,该触发器调用存储过程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 ;

测试交易

首先,从帐户ID 1中提取400:

CALL withdraw(1, 400);

有效。

其次,从帐户表中查询数据:

SELECT * FROM accounts
WHERE accountId = 1;

帐户ID 1的当前余额为600。

第三,从帐户ID 1中提取600:

CALL withdraw(1, 600);

存储过程Withdraw执行一条UPDATE语句,该语句自动调用触发器before_accounts_update。

然后,before_account_update触发器调用存储过程CheckWithdrawal来检查提现。
由于提款金额导致最小余额零(小于25),因此发出错误。

Error Code: 1644. Insufficient amount, the maximum withdrawable is 575.00

第四,从帐户ID 1中提取575:

CALL withdraw(1, 575);

最后,通过查询帐户表中的数据来验证提款:

SELECT * 
FROM accounts
WHERE accountId = 1;

在本教程中,您学习了如何从MySQL中的触发器调用存储过程。