只是一个简单的示例,下面的grigger和procedure功能差不多,在t表插入记录,同时在t1表的记录+1,只是procedure有返回值.
功能虽然很简单,但是大家稍微修改一下,功能就很强大了。
trigger
trigger和procedure示例_休闲delimiter //
trigger和procedure示例_休闲create trigger up after insert on t
trigger和procedure示例_休闲for each row
trigger和procedure示例_休闲BEGIN
trigger和procedure示例_休闲  set @count_name=(select count(1) from t1 where name = );
trigger和procedure示例_休闲  if    @count_name = 0 then
trigger和procedure示例_休闲    insert into t1(name,num) values(,1);
trigger和procedure示例_休闲  elseif
trigger和procedure示例_休闲    @count_name > 0 then
trigger和procedure示例_休闲    update t1 set num=num+1 where name=;
trigger和procedure示例_休闲  END IF;
trigger和procedure示例_休闲END;//
trigger和procedure示例_休闲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)