触发器是由事件来触发某个操作,事件包括insert,update,delect语句,重点学习创建,查看,删除。
以实验为例
1.在product表上分别创建AFTER INSERT、BEFORE UPDATE和BEFORE DELETE 3个触发器, 触发器的名称分别为product_af_insert、product_bf_update和Tproduct_bf_del。执行语句部分都是向operate表中插入操作方法和操作时间。
mysql> create trigger product_af_insert after insert -> on product for each row -> insert into operate values(null,'insert product',now()); Query OK, 0 rows affected mysql> create trigger product_bf_update before update -> on product for each row -> insert into operate values(null,'update product',now()); Query OK, 0 rows affected mysql> create trigger product_bf_del before delete -> on product for each row -> insert into operate values(null,'delete product',now()); Query OK, 0 rows affected
2.查看product_bf_del触发器的基本结构。
select * from information_schema.triggers where trigger_name='product_bf_del';
3.对product表分别执行如下INSERT、UPDATE和DELETE操作,分别查看operate表。
INSERT INTO product VALUES(2, '止血灵','止血', '北京止血灵制药厂','北京市昌平区');
UPDATE product SET address='天津市开发区' WHERE id=2;
DELETE FROM product WHERE id=2;
insert into product values(2,'止血灵','止血','北京止血灵制药厂','北京市昌平区'); select * from product; select * from operate; update product set address='天津市开发区' where id=2; select * from product; select * from operate; delete from product where id=2; select * from product; select * from operate;
4.删除product_bf_update触发器。
drop trigger product_bf_update;