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 ;