Home > Backend Development > PHP Tutorial > 怎么做两个表之间的触发器

怎么做两个表之间的触发器

WBOY
Release: 2016-06-13 13:24:39
Original
1069 people have browsed it

如何做两个表之间的触发器?
学生表:学号,姓名,年龄,系号,系名
系表:系号,系名,姓名,年龄。

如何在学生表中增加一条记录,系表中也随之自动增加。同理,删除怎么做?

------解决方案--------------------
当然可以!
你可以仿照这篇博文进行 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 ('张三')$
这段代码试下看
------解决方案--------------------

SQL code

以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>
Copy after login
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template