目前有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.高手们帮忙看看,是哪里出错了??怎么解决??
`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.高手们帮忙看看,是哪里出错了??怎么解决??
解决方案 »
- 关于innodb检查点与脏页的问题
- 有没有可能在不停服务情况下, 调整一个表的外键关系?
- MYSQL的超时连接问题
- 需要大量在MySQL数据库中实现独立于操作系统的目录(或者说树)的资料
- Mysql中除了用order by 对记录进行排序外,还有没有其它方式?
- limit 查询求教
- 写了一个SERVLET,调用一个MYSQL查询分页显示的BEAN,编译不通过,哪位给看看,实在是郁闷:)
- left查询表后加as和不加as的问题,请教!
- 询问一个CASE语句的问题!两组代码的区别和结果异同!
- 求解:关于mysql触发器的问题
- 求助——MySQL 拓展字段的实现
- 求一条SQL执行语句,试了半天,还是不行!
call sp_t2(1);结果是什么?
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;
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