CODEWITHSUNDEEP
mysqlsqlstored-procedurestriggers
Harish Kurup
Harish Kurup

Reputation: 7525

how to call StoredProcedure or Functions from a MySQL Trigger?

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.

Upvotes: 2

Views: 16885

Answers (1)

cristian
cristian

Reputation: 8744

Look here Mysql Trigger Syntax

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;

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:

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.)

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

Upvotes: 6

Related Questions