如何做两个表之间的触发器?
学生表:学号,姓名,年龄,系号,系名
系表:系号,系名,姓名,年龄。
如何在学生表中增加一条记录,系表中也随之自动增加。同理,删除怎么做?
------解决方案--------------------
当然可以!
你可以仿照这篇博文进行 http://www.cnblogs.com/nicholas_f/archive/2009/09/22/1572050.html
希望成功后能共享出你的成果
------解决方案--------------------
我没有做过,所以才让你去看人家的做的例子
要是让你看手册,不就太那个了吗
------解决方案--------------------
delimiter //
DROP TRIGGER IF EXISTS trigger_on_tab1//
CREATE TRIGGER trigger_on_tab1
AFTER INSERT ON test1
FOR EACH ROW
BEGIN
insert into test2(test1_id,test1_name) values(new.id, new.name);
END//
一个列子,可以借鉴下哦!
------解决方案--------------------
语法错了。贴出你的SQL串看看。
------解决方案--------------------
语句没错,应该是分界符的问题。
执行该触发器之前先将分节符;修改下再执行创建
delimiter $
drop trigger if exists t_afterinsert_on_tab1$
create trigger t_afterinsert_on_tab1
after insert on TAB1
for each row
begin
insert into tab2(tab2_name) values (new.tab1_name);
end$
insert into tab1 (tab1_name) values ('张三')$
这段代码试下看
------解决方案--------------------
以eschop的商品表,跟订单表为例: 新建商品表 create table goods( id int auto_increment primary key, #商品id name varchar(30) not null default '',#商品名 num tinyint not null default 0 #商品数量 )engine myisam default charset utf8; 新建订单变 create table indent( oid int auto_increment primary key, #订单id gid int not null default 0, #商品id much tinyint not null default 0 #购买数量 )engine myisam default charset utf8; mysql> desc goods; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | | | | num | tinyint(4) | NO | | 0 | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set mysql> desc indent; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | oid | int(11) | NO | PRI | NULL | auto_increment | | gid | int(11) | NO | | 0 | | | much | tinyint(4) | NO | | 0 | | +-------+------------+------+-----+---------+----------------+ 插入演示数据: insert into goods values(1,'三星手机',12),(2,'ipad电脑',19),(3,'摩托罗拉mp3',38); mysql> select * from goods; +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 38 | +----+-------------+-----+ 手工给订单表添加购买记录: insert into indent(gid,much)values(3,2); mysql> select * from indent; +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 3 | 2 | +-----+-----+------+ 1 row in set 手工给商品表减少商品信息: update goods set num=num-2 where id=3; mysql> select * from goods; +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 36 | +----+-------------+-----+ 3 rows in set 修改mysql的结束符: mysql> delimiter $ ------------------------------------------- 创建触发器 create trigger tg1 after insert #在插入之后触发 on indent for each row #固定写法 begin update goods set num=num-1 where id=3; end $ ------------------------------------------ 模拟用户下订单流程 商品表: +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 36 | +----+-------------+-----+ 订单表: +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 3 | 2 | +-----+-----+------+ ①下订单 insert into indent(gid,much)values(2,4)$ ②查看订单表 +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 3 | 2 | | 2 | 2 | 4 | +-----+-----+------+ ③商品表应该减少 +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 结论:显然用户下了2号订单,下了4件商品,订单生成了!商品却没减少,还出现了错误! ----------------------------------------- 正确的创建触发器: create trigger tg2 after insert on indent for each row begin update goods set num=num-new.much where id=new.gid; end $ -----------------------------------下订单insert触发器----------------------------------------- 出现错误:因为一张表不能同时被2个触发器监视,所以要删除开始创建的触发器 mysql> drop tg1$ Query OK, 0 rows affected mysql> show triggers$ Empty set 开始购买商品(清空订单表): mysql> select * from goods; -> $ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set mysql> insert into indent(gid,much)values(2,4)$ Query OK, 1 row affected mysql> select * from indent$ #下订单成功 +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in set mysql> select * from goods$ #对应商品自动减少OK +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 15 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set ------------------------------------------取消订单delete触发器------------------------------------ create trigger tg3 after delete on indent for each row begin update goods set num=num+old.much where id=old.gid; end $ 注:真项目中,永远不会物理删除订单 ---------------------------------- 模拟取消订单: mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 15 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set mysql> select * from indent$ +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in set mysql> delete from indent where oid=1$ Query OK, 1 row affected mysql> select * from indent$ Empty set mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set ------------------------------------修改订单update触发器----------------------------------------- 修改订单公式:update goods set num=num+old.much-new.much where id=old.gid; 关键部分:新数量等=本身数量+被修改的旧数量-新产生的数量(完全数学逻辑),id不变 create trigger tg4 after update on indent for each row begin update goods set num=num+old.much-new.much where id=old.gid; end $ ------------------------- mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 7 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set mysql> select * from indent $ +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 2 | 1 | 5 | +-----+-----+------+ 1 row in set mysql> update indent set much=10 where oid=2$ Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 2 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in set mysql> select * from indent$ +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 2 | 1 | 10 | +-----+-----+------+ 1 row in set ------------------------------------------- 触发器基础完成! 快速清空表:truncate [表名] 修改mysql的结束符:delimiter $; 显示触发器:show triggers 删除触发器:drop trigger [触发器名称] 创建触发器: create trigger [触发器名称] after [触发行为/insert/update/delete] on [监视对象/某张表] for each row #固定写法 begin sql语句; end $ 注意:一个触发器只能对应某张表的某一个行为!不能多个触发器来监视某一张表的同一个行为! <div class="clear"> </div>