create trigger t2_insert_trig after insert on t2 for each row update t1 set number=number+1 where id=new.tid;
create trigger t2_delete_trig after delete on t2 for each row update t1 set number=number-1 where id=old.tid;
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>
谢谢楼上回复 问题我已经解决了方法如下: 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;$$
原理明白了 楼上的都对 但是 我这里有批量删除和插入的 所以就用count了 分还是给大家 谢谢
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;
这样不行啊 错误码: 1415 Not allowed to return a result set from a triggerExecution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000
create trigger t2_insert_trig after insert on t2
for each row
update t1 set number=number+1 where id=new.tid;
create trigger t2_delete_trig after delete on t2
for each row
update t1 set number=number-1 where id=old.tid;
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>
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;$$
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;
这样不行啊
错误码: 1415
Not allowed to return a result set from a triggerExecution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000