mysql触发器

CREATE TRIGGER 创建触发器

 trig_book                数据表

AFTER INSERT   在insert 执行之后

on t_book FOR EACH ROW 执行一个操作

修改t_bookType 表的字段+1 匹配t_book中的bookTypeId和t_booktype.id字段中的哪一个需要+1

UPDATEt_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;

（new过度变量，如新插入的数据，old表示值为空的情况下，删除掉的数据）   

 

CREATE TRIGGER trig_book AFTER INSERT ON t_book FOR EACH ROW UPDATEt_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;    

       

插入一条数据

INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);

 

创建有多个执行语句的触发器

 

DELIMITER | 表示集合

CREATE TRIGGER trig_book2                                     创建触发器

AFTERDELETE                                                           删除动作

ON t_book FOR EACH ROW                                      做一个操作

BEGIN                                                                      开始

                                                                                在t_bookType 中bookNum-1 old删除t_booktype.id数据

UPDATE t_bookType SET bookNum=bookNum-1 WHEREold.bookTypeId=t_booktype.id;

                                                                                插入一条删除的数据

 INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');                                                                                                                                                          测试删除t_test.id的数据

DELETE FROM t_test WHERE old.bookTypeId=t_test.id;

   END |

DELIMITER;

 

DELIMITER|

CREATE TRIGGER trig_book2 AFTER DELETE

   ON t_book FOR EACH ROW

   BEGIN

      UPDATE t_bookType SET bookNum=bookNum-1 WHEREold.bookTypeId=t_booktype.id;

      INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');

      DELETE FROM t_test WHERE old.bookTypeId=t_test.id;

   END

|

DELIMITER;

DELETE FROM t_book WHERE id=5;

 

 

自己做了一个实验

t_a1表 

  

t_b1表

 

CREATE TRIGGER luweitest

AFTER INSERT

ON t_a1 FOR EACH ROW

UPDATE t_b1 SET total_id=total_id+1 WHERE new.book_name=t_b1.total ;

 

INSERT INTO t_a1 (book_name,name_Num)VALUES ('英语类','英语速成');

 插入后t_b1表英语类自动+1了

 

 

SHOW TRIGGERS; 查询触发器

 

DROP TRIGGER trig_book2 ; 删除触发器