Monday, May 9, 2011

Can you call a stored procedure from a trigger in MySQL?

Very often i am asked this question. The answer is a big yes. MySQL allows you to call a stored procedure within a trigger. The stored procedure can even be within another database. The following a simple example to prove the point.


**** Create First database and table ****
mysql> create database test2;
mysql> use test2;
mysql> create table emps( emp_id int, salary int);

**** Create Second database and table ****
mysql> create database test3;
mysql> use test3;
mysql> create table error(message varchar(100));

**** Create Stored Procedure ****
mysql> delimiter $$
mysql> create procedure exect(pass varchar(100))
    -> begin
    -> insert into error(message) values (pass);
    -> insert into error(message) values (pass);
    -> end$$
mysql> delimiter ;

**** Create Trigger ****
mysql> delimiter $$
mysql> create trigger cc before insert on test2.emps for each row
    -> begin
    -> if new.salary > 100 then
    -> call test3.exect("sorry");
    -> end if;
    -> end $$
mysql> delimiter ;

**** Test Feasibility****
mysql> insert into test2.emps values(1,1000);
mysql> select * from test3.error;
+---------+
| message |
+---------+
| sorry   |
| sorry   |
+---------+

which means the trigger can call a stored procedure in another database.

1 comment:

  1. Huuum, What's MySQL at first place? What's the difference with other DBMS like MS Access?

    Your example looks more like a program code than a DBMS!!!

    ReplyDelete