56,687
社区成员
发帖
与我相关
我的任务
分享mysql 触发器问题
有2个表 T1（id title number） T2（id tid title） t2表中tid外键关联t1表id
现在我想在t2表上建立个触发器，在执行 插入 和删除 操作后， 更新t1表的对应行的number （number为t2的总数 ti。id=t2.tid，相当于一个帖子和这个帖子的回复那种关系）
请问这个触发器要什么写啊？
现在我想在t2表上建立个触发器，在执行 插入 和删除 操作后， 更新t1表的对应行的number （number为t2的总数 ti。id=t2.tid，相当于一个帖子和这个帖子的回复那种关系）
请问这个触发器要什么写啊？
...全文
请发表友善的回复…
发表回复
火雨 2009-12-28
- 打赏
- 举报
还得请大家帮个忙
添加的时候可以了 现在有个问题 删除的时候 也要相应的计算number 怎么办
注意：t2表的记录 是可以批量删除的
添加的时候可以了 现在有个问题 删除的时候 也要相应的计算number 怎么办
注意：t2表的记录 是可以批量删除的
火雨 2009-12-28
- 打赏
- 举报
[Quote=引用 6 楼 forever_feng 的回复:]
CREATE TRIGGER tg_insert_t2 after INSERT ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
CREATE TRIGGER tg_delete_t2 after DELETE ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
[/Quote]
这样不行啊
错误码: 1415
Not allowed to return a result set from a trigger
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
CREATE TRIGGER tg_insert_t2 after INSERT ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
CREATE TRIGGER tg_delete_t2 after DELETE ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
[/Quote]
这样不行啊
错误码: 1415
Not allowed to return a result set from a trigger
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
平凡的思想者 2009-12-28
- 打赏
- 举报
CREATE TRIGGER tg_insert_t2 after INSERT ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
CREATE TRIGGER tg_delete_t2 after DELETE ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
CREATE TRIGGER tg_delete_t2 after DELETE ON t2
FOR EACH ROW BEGIN
DECLARE total int;
select total=sum(*) from t2 where t2.id = old.id;
update t1 set number = total where id = old.id;
END;
火雨 2009-12-28
- 打赏
- 举报
原理明白了 楼上的都对 但是 我这里有批量删除和插入的 所以就用count了 分还是给大家 谢谢
火雨 2009-12-28
- 打赏
- 举报
谢谢楼上回复 问题我已经解决了
方法如下：
DELIMITER $$
CREATE
TRIGGER `t2_ai` AFTER INSERT ON `t2`
FOR EACH ROW BEGIN
update t1 set number=(select count(NEW.tid) from t2 where t1.id=NEW.mes_id) where t1.id=NEW.mes_id;
END;$$
方法如下：
DELIMITER $$
CREATE
TRIGGER `t2_ai` AFTER INSERT ON `t2`
FOR EACH ROW BEGIN
update t1 set number=(select count(NEW.tid) from t2 where t1.id=NEW.mes_id) where t1.id=NEW.mes_id;
END;$$
ACMAIN_CHM 2009-12-28
- 打赏
- 举报
create trigger tr_t2_ai after insert on t2
for each row
update t1 set `number` =`number` +1 where id=new.tid;
create trigger tr_t2_ad after delete on t2
for each row
update t1 set `number` =`number` -1 where id=old.tid;mysql> create trigger tr_t2_ai after insert on t2
-> for each row
-> update t1 set `number` =`number` +1 where id=new.tid;
Query OK, 0 rows affected (0.19 sec)
mysql> create trigger tr_t2_ad after delete on t2
-> for each row
-> update t1 set `number` =`number` -1 where id=old.tid;
Query OK, 0 rows affected (0.16 sec)
mysql> select * from t1;
+------+-------+--------+
| id | title | number |
+------+-------+--------+
| 1 | a | 0 |
| 2 | b | 0 |
+------+-------+--------+
2 rows in set (0.00 sec)
mysql> insert into t2 values (1,1,'A');
Query OK, 1 row affected (0.39 sec)
mysql> select * from t1;
+------+-------+--------+
| id | title | number |
+------+-------+--------+
| 1 | a | 1 |
| 2 | b | 0 |
+------+-------+--------+
2 rows in set (0.00 sec)
mysql> insert into t2 values (2,1,'X');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t1;
+------+-------+--------+
| id | title | number |
+------+-------+--------+
| 1 | a | 2 |
| 2 | b | 0 |
+------+-------+--------+
2 rows in set (0.00 sec)
mysql> delete from t2 where id=2;
Query OK, 1 row affected (0.06 sec)
mysql> select * from t1;
+------+-------+--------+
| id | title | number |
+------+-------+--------+
| 1 | a | 1 |
+------+-------+--------+
1 row in set (0.00 sec)
mysql>
阿_布 2009-12-28
- 打赏
- 举报
create trigger t2_delete_trig after delete on t2
for each row
update t1 set number=number-1 where id=old.tid;
阿_布 2009-12-28
- 打赏
- 举报
create trigger t2_insert_trig after insert on t2
for each row
update t1 set number=number+1 where id=new.tid;
