修改mysql定界符
delimiter+定界符
eg:delimiter $
查看所有触发器
show triggers 定界符;
eg:show triggers $
删除触发器
drop trigger 触发器名称+定界符
eg:drop trigger tg1 $
创建触发器
注意:
- 同一个数据库的触发器名称不能重复
- 同一张表的同一个事件不能有两个触发器
以商品表和订单表为例
tp_goods
CREATE TABLE tp_goods (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '缂栧彿',
name varchar(255) NOT NULL COMMENT '鍟嗗搧鍚嶇О',
num int(10) unsigned NOT NULL COMMENT '搴撳瓨閲?,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
tp_orders
CREATE TABLE tp_orders (
oid int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '璁㈠崟id',
gid int(10) unsigned NOT NULL COMMENT '鍟嗗搧id',
much int(10) unsigned NOT NULL COMMENT '鍟嗗搧鏁伴噺',
PRIMARY KEY (oid)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
触发器1(添加数据):
监视订单表,插入数据后,修改商品表库存量
create trigger tg1
before insert on tp_orders
for each row
begin
-- 根据商品id 查询商品表的商品数量
-- 在mysql中定义变量
declare rnum int;
select num into rnum from tp_goods where id = new.gid;
if new.much>rnum
then set new.much = rnum;
end if;
update tp_goods set num=num-new.much where id = new.gid;
end$
触发器2(修改数据):
监视订单表,修改数据后,修改商品表的库存量
create trigger tg2
before update on tp_orders
for each row
begin
-- 查询商品表剩余库存量
declare rnum int;
select num into rnum from tp_goods where id = new.gid;
if rnum+old.much<new.much
then
set new.much = rnum + old.much;
end if;
update tp_goods set num = num + old.much - new.much where id = new.gid;
end$
触发器3(删除数据):
监视订单表, 删除数据后,修改商品表记录
create trigger tg3
after delete on tp_orders
for each row
begin
update tp_goods set num = num + old.much where id = old.gid;
end$
