MySQL触发器是MySQL数据库的重要概念,下面列出了几个MySQL触发器的例子,供您参考,希望对您学习mysql触发器方面能有所帮助。
MySQL触发器的声明
create trigger buy after/before insert/update/delete on 表名 for each row #固定的 begin sql1; sql2; sql3; end;
示例一
delimiter // create trigger InsertUser before insert on user for each row Begin insert into user_group(uid,gid) values(new.uid,'group4444444444'); end;// delimiter ;
示例二
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 ;
示例四
create trigger buy before insert on o for each row begin select num into @num from goods where gid = new.gid; if (new.much > @num) then set new.much = @num; end if; update goods set num = num - new.much where gid = new.gid; end
示例五
create trigger chkorder before insert on order_goods for each row begin select goods_number into @num from goods where goods_id = new.goods_id; if (new.goods_number > @num) then set @order_error = 1; else update goods set goods_number = goods_number - new.goods_number where goods_id = new.goods_id; end if; end
评论前必须登录!
注册