有两张表t1,与t2,我想在t2表创建一个INSERT触发器,在对t2进行insert操作时调用这个触发器,但是会报错,请看源码:-- 创建t1表
CREATE TABLE t1 (
`t1_id` int(11) NULL DEFAULT NULL ,
`t1_n` int(11) NULL DEFAULT NULL
);
-- 创建t2表
CREATE TABLE t2 (
`t1_id` int(11) NULL DEFAULT NULL ,
`t2_n1` int(11) NULL DEFAULT NULL ,
`t2_n2` int(11) NULL DEFAULT NULL
);
-- t1表插入数据
INSERT INTO t1 VALUES(1,5);
-- t2表创建INSERT触发器
DROP TRIGGER IF EXISTS tig_count;
CREATE TRIGGER tig_count
AFTER INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE v_t1_n INT;
SET @t1_id=NEW.t1_id;
SET @t2_n1=NEW.t2_n1;
SELECT t1_n INTO @t1_n FROM t1 WHERE t1_id=@t1_id;
UPDATE t2 SET t2_n2=v_t1_n*@t2_n1 WHERE t1_id=@t1_id;
END;然后在对t2进行insert操作时报错:INSERT INTO t2 (t1_id,t2_n1) VALUES(1,2);错误描述:
[Err] 1442 - Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
请问我是触发器写的有问题吗?高人请解答,谢谢!!!
CREATE TABLE t1 (
`t1_id` int(11) NULL DEFAULT NULL ,
`t1_n` int(11) NULL DEFAULT NULL
);
-- 创建t2表
CREATE TABLE t2 (
`t1_id` int(11) NULL DEFAULT NULL ,
`t2_n1` int(11) NULL DEFAULT NULL ,
`t2_n2` int(11) NULL DEFAULT NULL
);
-- t1表插入数据
INSERT INTO t1 VALUES(1,5);
-- t2表创建INSERT触发器
DROP TRIGGER IF EXISTS tig_count;
CREATE TRIGGER tig_count
AFTER INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE v_t1_n INT;
SET @t1_id=NEW.t1_id;
SET @t2_n1=NEW.t2_n1;
SELECT t1_n INTO @t1_n FROM t1 WHERE t1_id=@t1_id;
UPDATE t2 SET t2_n2=v_t1_n*@t2_n1 WHERE t1_id=@t1_id;
END;然后在对t2进行insert操作时报错:INSERT INTO t2 (t1_id,t2_n1) VALUES(1,2);错误描述:
[Err] 1442 - Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
请问我是触发器写的有问题吗?高人请解答,谢谢!!!
解决方案 »
- memory 引擎为什么点那么大的空间?
- 不显示删除回复显示所有回复显示星级回复显示得分回复 一个可能比较简单的问题,应用怎么访问mysql 主从库中 多个从库
- MYSQL编译时出现的问题
- 查询有多个商品订单的语句
- 表分区, 但需要字段unique 的问题
- 刚学MySQL,初级问题,关于Row Format的说明
- 请教一个建表是出现的问题
- 急切征求一条mysql语句!
- cluster SQL节点搭建小问题:Could not find ./bin/my_print_defaults
- 新手提问: mysql数据库中 对一条记录修改的同时 不希望有其他操作对这一行也有修改 这要怎么实现
- 看看为什么这个触发器会报错
- 单机运行MySQL要不要装IIS或APACHE?
DROP TRIGGER IF EXISTS tig_count;
CREATE TRIGGER tig_count
AFTER INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE v_t1_n INT,v_t1_id int,v_t2_n1 int;
SET v_t1_id=NEW.t1_id;
SET v_t2_n1=NEW.t2_n1;
SELECT t1_n INTO v_t1_n FROM t1 WHERE t1_id=v_t1_id;
SET NEW.t2_n2=v_t1_n*v_t2_n1 ;
END;
[Err] 1362 - Updating of NEW row is not allowed in after trigger
before INSERT ON t2
建议认真查看错误提示。
那我试试看。
DROP TRIGGER IF EXISTS tig_count;
CREATE TRIGGER tig_count
BEFORE INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE v_t1_n INT,v_t1_id int,v_t2_n1 int;
SET v_t1_id=NEW.t1_id;
SET v_t2_n1=NEW.t2_n1;
SELECT t1_n INTO v_t1_n FROM t1 WHERE t1_id=v_t1_id;
SET NEW.t2_n2=v_t1_n*v_t2_n1 ;
END;
CREATE TRIGGER tig_count
BEFORE INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE v_t1_n INT;
SET @t1_id=NEW.t1_id;
SET @t2_n1=NEW.t2_n1;
SELECT t1_n INTO @t1_n FROM t1 WHERE t1_id=@t1_id;
SET NEW.t2_n2=v_t1_n*@t2_n1 ;
END;