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 = );
  if    @count_name = 0 then
    insert into t1(name,num) values(,1);
  elseif
    @count_name > 0 then
    update t1 set num=num+1 where 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)