有两张表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.
请问我是触发器写的有问题吗?高人请解答,谢谢!!!
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;