mysql 触发器实例

1、当更新某个特定字段时触发操作 我要做的事情是，当我更新user表的name时，触发器同时更新comment表

delimiter ||      //mysql 默认结束符号是分号，当你在写触发器或者存储过程时有分号出现，会中止转而执行  
drop trigger if exists updatename||    //删除同名的触发器，  
create trigger updatename after update on user for each row   //建立触发器，  
begin  
//old,new都是代表当前操作的记录行，你把它当成表名，也行;  
if new.name!=old.name then   //当表中用户名称发生变化时,执行  
update comment set comment.name=new.name where comment.u_id=old.id;  
end if;  
end||  
delimiter ;

</pre><pre name="code" class="sql">DELIMITER $$
CREATE TRIGGER `tri_jb_update`
after update
on lh_jb_records for each row
begin
update lh_user set invite_id = 0 where id = new.user_id;
end$$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER `tri_team_ordernum_insert`
BEFORE  insert 
on lh_order_team for each row
begin
update lh_user set team_ordernums = team_ordernums + 1 where id = new.team_id;
end$$
DELIMITER ;

 
create trigger InsertUser    
before insert on user   
for each row    
Begin   
IF new.Type=2 then   
insert into user_group(uid,gid) values(new.uid,'group4444444444');    
else   
insert into user_group(uid,gid) values(new.uid,'group55555555555')    
END IF;    
end;//    
delimiter ;    
 
    
delimiter //    
create trigger InsertUser    
before insert on user   
for each row    
Begin   
IF new.type=1 then   
insert into user_group(uid,gid) values(new.uid,'578d3369633b47bd9c1fe8bf905cbfb1');    
END IF;    
IF new.type=2 then   
 insert into user_group(uid,gid) values(new.uid,'387bcd57fc5a4c3c9de83ee210fef661');    
END IF;    
end;//    
delimiter ;

delimiter $$    
drop trigger if exists tri_userstatus_update $$     
create trigger `tri_userstatus_update` after update on lh_user for each row     
begin  
 
if new.status!=old.status then  
set @ss = (select status from lh_user where id=new.id); 
set @up_s = (select status from lh_user where invite_id=new.id); 
<span style="white-space:pre">	</span>if @s>@up_s then
<span style="white-space:pre">	</span>update lh_user set `invite_id`=0,`beyond_time`=CURRENT_DATE where id=new.id;
<span style="white-space:pre">	</span>end if;
end if;  
end$$  
delimiter ;