mysql触发器——学习笔记及实验

liyanyinng 2021-09-07 原文

触发器是由事件来触发某个操作，事件包括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;