You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 这是什么意思? 版本不对吗?
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//一个列子,可以借鉴下哦!
为什么总是报错? You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 请问,如何解决?
语法错了。贴出你的SQL串看看。
这是代码 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_id) values(new.tab1_id); END;麻烦了
语句没错,应该是分界符的问题。 执行该触发器之前先将分节符;修改下再执行创建 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 affectedmysql> show triggers$ Empty set 开始购买商品(清空订单表): mysql> select * from goods; -> $ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in setmysql> insert into indent(gid,much)values(2,4)$ Query OK, 1 row affectedmysql> select * from indent$ #下订单成功 +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in setmysql> 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 setmysql> select * from indent$ +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in setmysql> delete from indent where oid=1$ Query OK, 1 row affectedmysql> select * from indent$ Empty setmysql> 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 setmysql> 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: 0mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 2 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in setmysql> 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 $ 注意:一个触发器只能对应某张表的某一个行为!不能多个触发器来监视某一张表的同一个行为!
新建商品表 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 affectedmysql> show triggers$ Empty set 开始购买商品(清空订单表): mysql> select * from goods; -> $ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 12 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in setmysql> insert into indent(gid,much)values(2,4)$ Query OK, 1 row affectedmysql> select * from indent$ #下订单成功 +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in setmysql> 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 setmysql> select * from indent$ +-----+-----+------+ | oid | gid | much | +-----+-----+------+ | 1 | 2 | 4 | +-----+-----+------+ 1 row in setmysql> delete from indent where oid=1$ Query OK, 1 row affectedmysql> select * from indent$ Empty setmysql> 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 setmysql> 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: 0mysql> select * from goods$ +----+-------------+-----+ | id | name | num | +----+-------------+-----+ | 1 | 三星手机 | 2 | | 2 | ipad电脑 | 19 | | 3 | 摩托罗拉mp3 | 35 | +----+-------------+-----+ 3 rows in setmysql> 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 $ 注意:一个触发器只能对应某张表的某一个行为!不能多个触发器来监视某一张表的同一个行为!
错误 SQL 查询: delimiter $ MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter $' at line 1只是定义了一个 $, 为什么还有错误?
英语不好,命名上希望你能看懂 s_name是学生名,x_number是系号,以此为例CREATE TABLE student ( `id` MEDIUMINT AUTO_INCREMENT, `s_number` INT(3), `s_name` VARCHAR(30), `x_number` INT(3), `x_name` VARCHAR(30), PRIMARY KEY(`id`) )CREATE TABLE department ( `s_number` INT(3), `s_name` VARCHAR(30), `x_number` INT(3), `x_name` VARCHAR(30) )delimiter // CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON `student` FOR EACH ROW BEGIN INSERT INTO `department`( `s_number`, `s_name`, `x_number`, `x_name`) VALUES( new.`s_number`, new.`s_name`, new.`x_number`, new.`x_name`); END //在我的机子上已经测试通过了,复制粘贴的时候注意BEGIN后面必须换行
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; delimiter $ CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW BEGIN insert into tab2(tab2_id) values(new.tab1_id); END $
愁死我了。 还是报错错误 SQL 查询: delimiter $ CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW BEGIN INSERT INTO tab2( tab2_id ) VALUES (new.tab1_id );MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter $CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 ' at line 1
你可以仿照这篇博文进行 http://www.cnblogs.com/nicholas_f/archive/2009/09/22/1572050.html希望成功后能共享出你的成果
这是什么意思?
版本不对吗?
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//一个列子,可以借鉴下哦!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 请问,如何解决?
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_id) values(new.tab1_id);
END;麻烦了
执行该触发器之前先将分节符;修改下再执行创建
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 affectedmysql> show triggers$
Empty set
开始购买商品(清空订单表):
mysql> select * from goods;
-> $
+----+-------------+-----+
| id | name | num |
+----+-------------+-----+
| 1 | 三星手机 | 12 |
| 2 | ipad电脑 | 19 |
| 3 | 摩托罗拉mp3 | 35 |
+----+-------------+-----+
3 rows in setmysql> insert into indent(gid,much)values(2,4)$
Query OK, 1 row affectedmysql> select * from indent$ #下订单成功
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 1 | 2 | 4 |
+-----+-----+------+
1 row in setmysql> 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 setmysql> select * from indent$
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 1 | 2 | 4 |
+-----+-----+------+
1 row in setmysql> delete from indent where oid=1$
Query OK, 1 row affectedmysql> select * from indent$
Empty setmysql> 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 setmysql> 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: 0mysql> select * from goods$
+----+-------------+-----+
| id | name | num |
+----+-------------+-----+
| 1 | 三星手机 | 2 |
| 2 | ipad电脑 | 19 |
| 3 | 摩托罗拉mp3 | 35 |
+----+-------------+-----+
3 rows in setmysql> 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
$
注意:一个触发器只能对应某张表的某一个行为!不能多个触发器来监视某一张表的同一个行为!
新建商品表
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 affectedmysql> show triggers$
Empty set
开始购买商品(清空订单表):
mysql> select * from goods;
-> $
+----+-------------+-----+
| id | name | num |
+----+-------------+-----+
| 1 | 三星手机 | 12 |
| 2 | ipad电脑 | 19 |
| 3 | 摩托罗拉mp3 | 35 |
+----+-------------+-----+
3 rows in setmysql> insert into indent(gid,much)values(2,4)$
Query OK, 1 row affectedmysql> select * from indent$ #下订单成功
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 1 | 2 | 4 |
+-----+-----+------+
1 row in setmysql> 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 setmysql> select * from indent$
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 1 | 2 | 4 |
+-----+-----+------+
1 row in setmysql> delete from indent where oid=1$
Query OK, 1 row affectedmysql> select * from indent$
Empty setmysql> 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 setmysql> 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: 0mysql> select * from goods$
+----+-------------+-----+
| id | name | num |
+----+-------------+-----+
| 1 | 三星手机 | 2 |
| 2 | ipad电脑 | 19 |
| 3 | 摩托罗拉mp3 | 35 |
+----+-------------+-----+
3 rows in setmysql> 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
$
注意:一个触发器只能对应某张表的某一个行为!不能多个触发器来监视某一张表的同一个行为!
delimiter $; 将分界符定义为$,这个符号随便选择,尽量选择不常用的字符,在每一个sql分句之后加在后面
比如(drop...)这个分句后面加上$;(ceate...end)作为一个单独的分句后面也加上$,这样那段sql运行时就不会中断了
谢谢
SQL 查询: delimiter $ MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter $' at line 1只是定义了一个 $,
为什么还有错误?
(
`id` MEDIUMINT AUTO_INCREMENT,
`s_number` INT(3),
`s_name` VARCHAR(30),
`x_number` INT(3),
`x_name` VARCHAR(30),
PRIMARY KEY(`id`)
)CREATE TABLE department
(
`s_number` INT(3),
`s_name` VARCHAR(30),
`x_number` INT(3),
`x_name` VARCHAR(30)
)delimiter //
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON `student`
FOR EACH ROW
BEGIN
INSERT INTO `department`( `s_number`, `s_name`, `x_number`, `x_name`)
VALUES( new.`s_number`, new.`s_name`, new.`x_number`, new.`x_name`);
END
//在我的机子上已经测试通过了,复制粘贴的时候注意BEGIN后面必须换行
和php的自定义定界符是等价的
echo <<< DOF
xxx
EOF;这就将
<<< DOF
EOF;
之间的内容当成了一个整体mysql的
delimiter $
$
也是同样的道理
你#19
只有开始
delimiter $
却没有结束
当然就要报错了
就好像php程序中的条件语句,只有开始的 {
却没有结束的 }
一样,不是要报错的吗
和php的自定义定界符是等价的
echo <<< DOF
xxx
EOF;这就将
<<< DOF
EOF……
[/Quote]请问,如何定义呢?
谢谢了
delimiter $
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id) values(new.tab1_id);
END
$
愁死我了。
还是报错错误
SQL 查询: delimiter $ CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1
FOR EACH
ROW
BEGIN
INSERT INTO tab2( tab2_id )
VALUES (new.tab1_id
);MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter $CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
' at line 1