Mysql中触发器问题

[nightgoblin]

目前有2张表t1，t2，源码如下：

CREATE TABLE t1 (
`t1_id`  int(11) NULL DEFAULT NULL ,
`t1_n`  int(11) NULL DEFAULT NULL 
);
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插入数据时，插入t2表数据同时也更新t1表数据。源代码如下：

USE test;
DROP PROCEDURE IF EXISTS sp_t2;
CREATE PROCEDURE sp_t2
(IN v_t1_id INT)
BEGIN
DECLARE v_t1_n INT;
DECLARE v_t2_n1 INT;
DECLARE v_t2_n2 INT;
SELECT t1_n INTO v_t1_n FROM t1 WHERE t1_id=v_t1_id;
SET v_t2_n1=v_t1_n*v_t1_id;
SET v_t2_n2=v_t1_n*v_t1_id;
UPDATE t1 SET t1_n=v_t2_n1 WHERE t1_id=v_t1_id;
INSERT INTO t2 VALUES(v_t1_id,v_t2_n1,v_t2_n2);
END;

然后t1表插入数据，源码：

INSERT INTO t1 VALUES (1,6);

然后提示报错：
[Err] 1442 - Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

高手们帮忙看看，是哪里出错了？？怎么解决？？

[vipper23]

到底是触发器还是存储过程？ call sp_t2(1)；结果是什么？

[nightgoblin]

引用 楼主 nightgoblin 的回复:

目前有2张表t1，t2，源码如下：

CREATE TABLE t1 (
`t1_id`  int(11) NULL DEFAULT NULL ,
`t1_n`  int(11) NULL DEFAULT NULL 
);
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插入数据时，插入t2表数据同时也更新t1表数据。源代码如下：

USE test;
DROP PROCEDURE IF EXISTS sp_t2;
CREATE PROCEDURE sp_t2
(IN v_t1_id INT)
BEGIN
DECLARE v_t1_n INT;
DECLARE v_t2_n1 INT;
DECLARE v_t2_n2 INT;
SELECT t1_n INTO v_t1_n FROM t1 WHERE t1_id=v_t1_id;
SET v_t2_n1=v_t1_n*v_t1_id;
SET v_t2_n2=v_t1_n*v_t1_id;
UPDATE t1 SET t1_n=v_t2_n1 WHERE t1_id=v_t1_id;
INSERT INTO t2 VALUES(v_t1_id,v_t2_n1,v_t2_n2);
END;

然后t1表插入数据，源码：

INSERT INTO t1 VALUES (1,6);

然后提示报错： [Err] 1442 - Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 高手们帮忙看看，是哪里出错了？？怎么解决？？

啊啊，不好意思，忘记个触发器源码了，补上：

USE test;
DROP TRIGGER IF EXISTS tig_count; 
CREATE TRIGGER tig_count
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
DECLARE v_t1_id INT;
DECLARE v_t1_n INT;
SET v_t1_id=NEW.t1_id;
CALL sp_t2(v_t1_id);
END; 

[nightgoblin]

引用 1 楼 vipper23 的回复:

到底是触发器还是存储过程？ call sp_t2(1)；结果是什么？

不好意思忘记触发器了，我重新发个帖子好了。谢谢！

[vipper23]

触发器中不能对本表进行操作 CREATE TRIGGER `traa` BEFORE INSERT ON `t1` FOR EACH ROW begin insert t2 (t1_id,t2_n1,t2_n2) values (new.t1_id,new.t1_id*new.t1_n,new.t1_id*new.t1_n); set new.t1_n=new.t1_id*new.t1_n; end