只是一个简单的示例，下面的grigger和procedure功能差不多，在t表插入记录，同时在t1表的记录+1,只是procedure有返回值.
功能虽然很简单，但是大家稍微修改一下，功能就很强大了。
trigger
delimiter
//
create trigger up after insert on t
for each row
BEGIN
set @count_name=(select count(1) from t1 where name = NEW.name);
if @count_name = 0 then
insert into t1(name,num) values(NEW.name,1);
elseif
@count_name > 0 then
update t1 set num=num+1 where name=NEW.name;
END IF;
END;
//
delimiter ；
procedure
delimiter //
create procedure up_pro ( in in_name varchar(30),out out_num int)
MODIFIES SQL DATA
BEGIN
SET @count_name = ( select count(1) from t1 where name = in_name);
INSERT INTO t( name) values(in_name);
if @count_name = 0 then
insert into t1( name,num) values (in_name,1);
else
set @num = ( select num from t1 where name = in_name);
update t1 set num=@num+1 where name=in_name;
END IF;
select num into out_num from t1 where name=in_name;
END;//
delimiter ;
create procedure up_pro ( in in_name varchar(30),out out_num int)
MODIFIES SQL DATA
BEGIN
SET @count_name = ( select count(1) from t1 where name = in_name);
INSERT INTO t( name) values(in_name);
if @count_name = 0 then
insert into t1( name,num) values (in_name,1);
else
set @num = ( select num from t1 where name = in_name);
update t1 set num=@num+1 where name=in_name;
END IF;
select num into out_num from t1 where name=in_name;
END;//
delimiter ;
t表
mysql> show
create
table t\G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
` name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
` name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
t1表
mysql> show
create
table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
` name` varchar(30) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
` name` varchar(30) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
本文转自 fenghao.cn 51CTO博客，原文链接：http://blog.51cto.com/linuxguest/477318，如需转载请自行联系原作者
