SQL 触发器详解及代码演示

1. 触发器的作用

触发器主要用于监视某个表的insert、update以及delete等更新操作,这些操作可以分别激活该表的insert、update或者delete类型的运行语句,从而实现数据的自动维护。

2. 创建触发器

创建一个触发器的语法如下:

create trigger [触发器名] [触发时机] [触发事件]
on [要监控的表名] for each row 
begin
	[要执行的语句]
end

触发时机包括 before和after,触发事件包括insert、update和delete,二者的组合,如before delete表示在删除前执行激活触发器,然后执行触发器中写定的代码。

例如下例代码,表示创建一个名为tri_test的触发器,它将会在prot_subscribe表执行插入操作后,执行begin…end中的代码(insert语句):

create trigger tri_test after insert on prot_subscribe for each row
begin
	insert into prot_log(mobile, id) values (new.mobile, new.product_id);
end

3. 查看触发器

3.1. show语句查看触发器

查看触发器的语法如下:

-- 查看指定数据库中的所有触发器
show triggers from [数据库名];

-- 或者,查看当前数据库中已定义的所有触发器
show triggers;

例如,要查看数据库sql_test中定义的触发器,则使用语句:

show triggers from sql_test;

返回的结果就是sql_test表中已定义的触发器的属性

3.2. information.schema表查看触发器

在MySQL系统中,在系统数据库information_ schema中存在一个存储所有触发器信息的系统表,查询该表格的记录也可以实现查看触发器功能。

例如

select * from information_schema.triggers;

返回的结果就是mysql中已定义的所有数据库

4. 删除触发器

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

drop trigger [if exists] [触发器名]

例如,下面代码表示,如果在sqltest数据库中存在tri_sub_one触发器,则删除它:

drop trigger if exists sqltest.tri_sub_one;

5. 使用限制

1、一个表上只能有一个相同类型的触发器,因此在一个表上最多只能建立6个触发器,分别是:

before insertafter insert
before updateafter update
before deleteafter delete

2、new表示触发器的所在表中,激活了触发器的那一行数据。
具体地,在insert型触发器中,new关键字用来表示将要(before)或已经(after)插入的新数据;在UPDATE型触发器中,old关键字用来表示将要或已经被修改的原数据,new用来表示将要或已经修改为的新数据;在delete型触发器中,old用来表示将要或已经被删除的原数据;

使用方法: new.[字段名]。另外,old是只读的,而new则可以在触发器中使用set语句进行赋值,这样不会再次触发触发器,造成循环调用。

3、对于mysql而言,一般情况下,默认是以分好" ; "作为结束执行语句,这导致在触发器的begin…end语句中有多行代码时,与触发器中需要的分行起冲突,为解决此问题,mysql提供了delimiter关键字,如:
" delimiter || “,表示在下面的代码段中,将结束符号变成||。当触发器创建完成后,可以用” delimiter ; “来将结束符号变回分号” ; "

-- 用$$符号来表示语句结束
delimiter $$ 
drop trigger if exists `updateegopriceondelete`$$ 
create 
    trigger `updateegopriceondelete` after  delete on  `customerinfo` 
    for each row begin 
delete from egoprice  where customerid=old.customerid; 
    end $$ -- 语句结束
delimiter ;  -- 将结束符号由$$ 变回分号;
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页