MySQL中建立一个update触发器，如果某字段变为0则删去该行

显示全部楼层 · 2011-4-23 22:23:07

假设有个表t
CREATE TABLE t (id INT, count INT);
我希望建立一个触发器，当表t被更新时，如果字段count被设置为0，则删去该行
该如何做？

千问 · 2011-4-23 22:23:07

mysql> CREATE TABLE t (id INT, count INT);Query OK, 0 rows affected (0.05 sec)mysql> DELIMITER //mysql> CREATE TRIGGER BeforeUpdateT
-> AFTER UPDATE ON t
-> FOR EACH ROW
-> BEGIN
-> IF new.count = 0 THEN
->
DELETE FROM t WHERE id = new.id;
-> END IF;
-> END;
-> //Query

千问 · 2011-4-23 22:23:07

mysql> delimiter //mysql> CREATE TRIGGER t_trigger AFTER UPDATE ON t
-> FOR EACH ROW
-> BEGIN
-> DELETE FROM t WHERE count=0 AND id =NEW.id;
-> END;//mysql>

千问 · 2011-4-23 22:23:07

createtrigger deletecount on
tfor insert, update as begin
deletet fromt,deleted wheret.[id]=deleted.[id]and t.count=0end