(2.13)Mysql之SQL基础——触发器

(2.13)Mysql之SQL基础——触发器

关键词:Mysql触发器

 

【1】触发器

MySQL语句在需要时被执行,存储过程也是如此,如果希望某条语句(或某些语句)在事件发生时自动执行,这就需要用到触发器。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于begin和end语句之间的一组语句):

delete;

insert;

update;

其他MySQL语句不支持触发器。。。

【1.0】创建触发器

需要以下4条信息:

①唯一的触发器名;

②触发器关联的表;

③触发器应该响应的活动(delete、insert或update);

④触发器何时执行(处理之前或之后);

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name:触发器的名称
tirgger_time:触发时机,为BEFORE或者AFTER
trigger_event:触发事件,为INSERT、DELETE或者UPDATE
tb_name:表示建立触发器的表明,就是在哪张表上建立触发器
trigger_stmt:触发器的程序体,可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器:
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

 

PS:MySQL中,触发器名必须在每个表中唯一,但不限制每个数据库中唯一,即:同一数据库中两个表可以具有相同名字的触发器(其他的DBMS中不被允许)。

触发器使用create teigger语句创建,下面是一个简单的例子:

create trigger newproduct after insert on products

for each row select 'product added';

这里创建了一个newproduct的新触发器,给出了after insert,所以此触发器在insert语句成功执行后执行;其中还指定了for each row,因此代码对每个插入行执行。

PS:只有表才支持触发器,视图不支持(临时表也不支持)。

    触发器按每个表每个事件每次的定义,每个表每个事件每次只允许一个触发器;因此每个表最多支持6个触发器(每条insert、update和delete的之前和之后),单一触发器不能与多个事件或多个表关联。

    如果before触发器失败,则MySQL将不执行请求的操作;此外,如果before触发器或语句本身失败,MySQL将不执行after触发器(如果有的话)。

【1.1】触发器的触发条件

  

【1.2】删除触发器

删除触发器使用drop trigger语句,例如:

drop trigger newproduct;

PS:触发器不能更新或覆盖;为了修改一个触发器,必须先删后建。

 

【2】使用触发器

【2.1】new和old的使用

  

【2.2】一般形式

-- 0.查看触发器1】SHOW TRIGGERS;
【2SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='trg_bm_history';


-- 1.一般形式
create trigger trigger_name trigger_time
trigger_event on table_name
for each row
begin
    trigger_statement;    
end

解析:
(1)trigger_time:触发时间,取值[before/after]即事件发生之前/之后
(2)trigger_event:触发时间,取值[insert/update/delete]即进行增//改事件触发该触发器;
    (注意replace以及load data等同于Insert事件)
(3for each row:关键字,对每一行进行触发;以行为单位;
(4)trigger_statement:触发执行语句
(5)old / new 关键字:可以获取到操作之前、操作之后的行数据,一般old用于after时刻,new多用于before时刻
  【1】insert操作:一般只使用new,new为新插入的行的数据
  【2】update操作:一般使用new和old,new为更新后的数据行,old为更新前的数据行
  【3】delete操作:一般只用old,表示删除前的数据行

-- 2.简单案例
#删除表之前使用触发器把被删除的表插入到历史表备份
delimiter //
create trigger trg_bm_history after
delete on bm
for each row
begin
  insert into bm_history values(old.b_id,old.b_name);
end //
delimiter ;

-- 3.删除
drop trigger trigger_name;

-- 4.注意事项1】对于相同的表,相同的事件只能创建一个触发器,比如对表 bm 创建了after delete 触发器,就不能再对该表创建 after delete 触发器了;

 

 

【2.3】insert触发器

使用insert触发器,需要知道以下三点:

在insert触发器代码内,可引用一个名为new的虚拟表,访问被插入的行;

在before insert触发器内,new中的值也可以被更新(允许更改被插入的值);

对于auto increment列,new在insert执行之前包含0,在insert执行之后包含新的自动生成值;

例子如下:

create trigger neworder before insert on orders
for each row 
begin
  set new.order_num=1; -- 修改将要插入插入的新行所在 order_num列值为1,插入进去的值就是1了。
end

 

 

此代码创建一个名为neworder的触发器,按照after insert on order执行;在插入一条新的数据orders表时,MySQL生成一个新订单号并保存到order_num中;

触发器从new.order_num取这个值并返回它;此触发器必须按照after insert执行,因为在before insert语句执行之前,新order_num还没生成。

PS:通常将before用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据)。这个规则也适用于update触发器。

【2.4】delete触发器

使用delete触发器,需要知道以下两点:

在delete触发器代码内,可以引用一个名为old的虚拟表,访问被删除的行;

old中的值全都是只读的,不能更新;

下面是一个使用old保存将要被删除的行到一个存档表中:

 

create trigger deleteorder before delete on orders
for each row
begin
insert into archive_orders(order_num,order_date,cust_id)
values(OLD_order_num, OLD_order_date, OLD_cust_id);
end;

 

  此代码在任意orders表中的数据被删除前执行此触发器;它使用一条insert语句将old中的值(要被删除的数据)保存到一个名为archive_orders的存档表中;

  上面的例子中,使用begin end块的好处是触发器能容纳多条SQL语句。

  PS:使用before delete触发器的优点:如果由于某些原因,数据不能存档,delete本身将被放弃。

【2.5】update触发器

使用update触发器,需要知道以下三点:

在update触发器代码内,可以引用一个名为old的虚拟表访问以前(update语句前)的值,引用一个名为new的虚拟表访问新更新的值;

在before update触发器中,new中的值可能也被更新(允许更改将要用于update语句中的值);

old中的值全都是只读的,不能更新;

下面的例子,保证了州名称缩写总是大写:

create trigger updateevendor before update on vendors

for each row set.vend_state = upper(new.vend_state);

这个例子中,每次更新一个行时,new.vend_state中的值(将用来更新表行的值)都用upper(new.vend_state)替换。

 

△关于触发器一些必须知道的知识:

创建触发器可能需要特殊的安全访问权限,但触发器的执行是自动的;如果insert、update、delete语句可以执行,则相应触发器也能执行;

应该用触发器来保证数据的一致性(大小写、格式等);有点在于它总是进行这种处理,而且是透明的进行,与客户机应用无关;

触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改记录到另一个表非常容易;

MySQL触发器不支持call语句,即不能从触发器内调用存储过程,所需的存储过程代码需要复制到触发器内。

 

案例:插入数据前,记下当条记录的其插入id+1;

delimiter //
create trigger trg_bm_max before 
insert on bm
for each row 
begin 
    update dm_count set count_max=new.b_id+1; 
end //
delimiter ;

 

【3】最佳实践

【3.1】当表新增行后,修改该表新增行中的某个字段值

delimiter //
create trigger trg_update_prefix_app before
insert on prefix_app
for each row
begin
  set new.in_icon=replace(new.in_icon,'oss-cn-zhangjiakou-internal.aliyuncs.com','firzjk.oss-cn-zhangjiakou.aliyuncs.com');
end //
delimiter ;

 

参考:https://www.cnblogs.com/zh-1721342390/p/9602941.html

posted @ 2019-02-28 01:13  郭大侠1  阅读(527)  评论(0编辑  收藏  举报