mysql ´¥·¢Æ÷ÎÊÌâ

ÓÐ2¸ö±í T1£¨id  title  number£©  T2£¨id tid title£©  t2±íÖÐtidÍâ¼ü¹ØÁªt1±íid

ÏÖÔÚÎÒÏëÔÚt2±íÉϽ¨Á¢¸ö´¥·¢Æ÷£¬ÔÚÖ´ÐÐ ²åÈë ºÍɾ³ý ²Ù×÷ºó£¬ ¸üÐÂt1±íµÄ¶ÔÓ¦ÐеÄnumber £¨numberΪt2µÄ×ÜÊý ti¡£id=t2.tid£¬Ï൱ÓÚÒ»¸öÌû×ÓºÍÕâ¸öÌû×ӵĻظ´ÄÇÖÖ¹Øϵ£©


ÇëÎÊÕâ¸ö´¥·¢Æ÷Ҫʲôд°¡£¿

---

SQL code:

create trigger t2_delete_trig after delete on t2
for each row
update t1 set number=number-1 where id=old.tid;

---

SQL code:
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;


SQL code:
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;
+------+-------