1.触发器执行单条语句:
CREATE TRIGGER trig_book AFTER INSERT //创建触发器,触发器在 t_book里面插入一条数据时候执行
ON t_book FOR EACH ROW
UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;//是的类型表里该分类的数量加一;new关键字表示新增
INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);//在t_book表里面插入一条数据
2.触发器执行多条语句
DELIMITER | //分隔符开始
CREATE TRIGGER trig_book2 AFTER DELETE //创建触发器,在t_book表里面删除一条数据时候执行
ON t_book FOR EACH ROW
BEGIN
UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;//将类型表里该类数量减少一;old表示删除一条数据
INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');//t_book的数据删除一条
DELETE FROM t_test WHERE old.bookTypeId=t_test.id;//log表插入一条数据
END
|
DELIMITER ;//分隔符结尾
3.删除触发器
DELETE FROM t_book WHERE id=5;
4.显示所有的触发器
SHOW TRIGGERS;