如何从MySQL触发器调用StoredProcedure或函数? [英] how to call StoredProcedure or Functions from a MySQL Trigger?

问题描述

我正在使用MySQL 5.1.3并使用PHPMyAdmin 3.1.3.1对其进行访问.用PHP作为服务器端脚本语言.我的问题语句是我们可以从Trigger语句中调用存储过程或函数，以便在调用INSERT | UPDATE | DELETE触发器时，它根据定义的逻辑调用SP以更新其他表.

I am working on MySQL 5.1.3 and using PHPMyAdmin 3.1.3.1 to access it. With PHP as the Server side scripting Language. My problem statement is can we call a Stored Procedure or Function from the Trigger statement so that when ever an INSERT|UPDATE|DELETE trigger is called, it calls the SP for updating some other tables according to the logic defined.

推荐答案

查看此处 MySQL触发器语法

mysql> delimiter //  
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account  
    -> FOR EACH ROW  
    -> BEGIN  
    ->     IF NEW.amount < 0 THEN  
    ->         SET NEW.amount = 0;  
    ->     ELSEIF NEW.amount > 100 THEN  
    ->         SET NEW.amount = 100;  
    ->     END IF;  
    -> END;//  
mysql> delimiter;  

分别定义存储过程，然后使用简单的CALL语句从触发器调用存储过程会更容易.如果要从多个触发器中调用相同的例程，这也将是有利的.

It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to invoke the same routine from within several triggers.

对于触发器在被激活时执行的语句中可能出现的内容有一些限制:

There are some limitations on what can appear in statements that a trigger executes when activated:

触发器不能使用CALL语句来调用将数据返回到客户端或使用动态SQL的存储过程. (允许存储过程通过OUT或INOUT参数将数据返回到触发器.)

The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.)

触发器不能使用显式或隐式开始或结束事务的语句，例如START TRANSACTION，COMMIT或ROLLBACK.

The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.

这篇关于如何从MySQL触发器调用StoredProcedure或函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！