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 ; 删除触发器