创建与使用触发器
1 创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件
ON 表名 FOR EACH ROW 执行语句
eg:t_book表中每次增加一个记录,t_booktype表对应id的数目加1
CREATE TRIGGER trig_book
AFTER INSERT
ON t_book FOR EACH ROW
UPDATE t_booktype
SET bookNum=bookNum+1
WHERE new.bookTypeId=t_booktype.id;
测试触发器:INSERT INTO t_book VALUES(NULL,'javajava',100,'me',1)
2 创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END
eg:DELIMITER 使用是因为有多个执行语句,默认读到'" ; "就去执行了
DELIMITER |
CREATE TRIGGER trig_book2
AFTER DELETE ON t_book FOR EACH ROW
BEGIN
UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
INSERT INTO t_log VALUES(NULL,NOW(),'在t_book表删除了一条数据');
DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
END
|
DELIMITER ;
测试:
DELETE FROM t_book WHERE id=1;