trigger和procedure示例

只是一个简单的示例，下面的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 ;

 

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

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)

 

 

转载于:https://blog.51cto.com/linuxguest/477318