当前位置: 澳门新濠3559 > 数据库 > 正文

触发器用来实现在永久表上进行某些操作时触发

时间:2019-11-09 00:17来源:数据库
触发器用来实现在永久表上进行某些操作时触发启动另一操作。 mysql 触发器(trigger),mysqltrigger 触发器(trigger):监视某种情况,并触发某种操作。 触发器创建语法四要素:1.监视地点(

触发器用来实现在永久表上进行某些操作时触发启动另一操作。

mysql 触发器(trigger),mysqltrigger

触发器(trigger):监视某种情况,并触发某种操作。

触发器创建语法四要素:1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件

语法:

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。

trigger_time是触发程序的动作时间。它可以是before或after,以指明触发程序是在激活它的语句之前或之后触发。

trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:

   insert :将新行插入表时激活触发程序,例如,通过insert、load data和replace语句。

  update:更改某一行时激活触发程序,例如,通过update语句。

     delete :从表中删除某一行时激活触发程序,例如,通过delete和replace语句。

要注意,trigger_event与以表操作方式激活触发程序的SQL语句并不很类似,这点很重要。

例如:关于insert的before触发程序不仅能被insert语句激活,也能被load data语句激活。

create trigger triggerName

after/before insert/update/delete on 表名

for each row   #这句话在mysql是固定的

begin

sql语句;

end;


 

对于insert语句, 只有new是合法的;

对于delete语句,只有old才合法;

对于update语句,new、old可以同时使用。


 

创建表(触发器要操作的两张表)

/*auto_increment:自增;priamry key :主键;comment:注释*/

/* drop:删除;if exists xxx(判断xxx名在数据库时候是否出存在xxx名称)*/

/* for each row :循环一行一行的执行数据 */

/* after insert/update/delete on table_name :针对哪个表执行的insert/update/delete 操作 *触发器用来实现在永久表上进行某些操作时触发启动另一操作,  --触发器是属于某一个表的。/

drop table if exists table1;

create table table1(
id int(4) primary key auto_increment not null comment 'id',
name varchar(225) comment '名字'
);

drop table if exists table2;
create table table2(
id int primary key auto_increment not null comment 'id',
name varchar(225) comment '名字'
);

Before与After区别:

before:(insert、update)可以对new进行修改,after不能对new进行修改,两者都不能修改old数据。

insert 触发器

drop trigger if exists insert_on_table1;
create trigger insert_on_table1
after insert  on table1
for each row
begin
insert into table2(name) value(new.name);
end

操作触发器

insert table1(name) value('aaa');

查询table2是否有值

select * from table2;

澳门新濠3559,delete触发器

drop trigger if exists delete_on_table1;
create trigger delete_on_table1
after delete on table1
for each ROW
begin
delete from table2 where name=old.name;
end

执行删除操作

delete from table1 where id=1;

查询table2变化

select * from table2;

更新table1更新触发器

drop trigger if exists update_on_table1;
create trigger update_on_table1
after update on table1
for each ROW
begin
update table2 set name=new.name where name=old.name;
end

执行更新操作

update table1 set name='ccc';

查询table2变化

select * from table2;

 使用before 统计插入积分例子:

创建表

drop table if exists table3;
create table table3(
id int primary key auto_increment comment 'id',
num int  comment '积分'
)engine=myisam  default charset=utf8 comment='单独积分表';

创建用函数变量接收的触发器

drop trigger if exists insert_on_table3;
create trigger insert_on_table3
before insert on table3
for each row 
set @sum=@sum+new.num;

执行触发器

set @sum=0;
insert into table3 values(1,2),(2,3),(3,3),(4,3);
select @sum;

 

触发器(trigger),mysqltrigger 触发器(trigger):监视某种情况,并触发某种操作。 触发器创建语法四要素:1.监视地点(table)2.监视事件(inse...

语法

MySQL触发器

触发器(trigger):监视某种情况,并触发某种操作。

触发器创建语法四要素:1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)

 

~~语法~~

CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。

 

create trigger triggerName

after/before insert/update/delete on 表名

for each row #这句话在mysql是固定的

begin

sql语句;

end;

注:各自颜色对应上面的四要素。

首先我们来创建两张表:

#商品表

create table g

(

  id int primary key auto_increment,

  name varchar(20),

  num int

);

#订单表

create table o

(

  oid int primary key auto_increment,

  gid int,

much int

);

insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);

 

如果我们在没使用触发器之前:假设我们现在卖了3个商品1,我们需要做两件事

1.往订单表插入一条记录

insert into o(gid,much) values(1,3);

2.更新商品表商品1的剩余数量

update g set num=num-3 where id=1;

 

现在,我们来创建一个触发器:

需要先执行该语句:delimiter $(意思是告诉mysql语句的结尾换成以$结束)

create trigger tg1
after insert on o
for each row
begin
update g set num=num-3 where id=1;
end$

这时候我们只要执行:

insert into o(gid,much) values(1,3)$

会发现商品1的数量变为7了,说明在我们插入一条订单的时候,触发器自动帮我们做了更新操作。

 

但现在会有一个问题,因为我们触发器里面num和id都是写死的,所以不管我们买哪个商品,最终更新的都是商品1的数量。比如:我们往订单表再插入一条记录:insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了,而商品2的数量没变,这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。

我们如何在触发器引用行的值,也就是说我们要得到我们新插入的订单记录中的gid或much的值。

对于insert而言,新插入的行用new来表示,行中的每一列的值用new.列名来表示。

所以现在我们可以这样来改我们的触发器

create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
end$

第二个触发器创建完毕,我们先把第一个触发器删掉

drop trigger tg1$

再来测试一下,插入一条订单记录:insert into o(gid,much) values(2,3)$

执行完发现商品2的数量变为7了,现在就对了。

 

现在还存在两种情况:

1.当用户撤销一个订单的时候,我们这边直接删除一个订单,我们是不是需要把对应的商品数量再加回去呢?

2.当用户修改一个订单的数量时,我们触发器修改怎么写?

我们先分析一下第一种情况:

监视地点:o表

监视事件:delete

触发时间:after

触发事件:update

对于delete而言:原本有一行,后来被删除,想引用被删除的这一行,用old来表示,old.列名可以引用被删除的行的值。

那我们的触发器就该这样写:

create trigger tg3

after delete on o

for each row

begin

update g set num = num + old.much where id = old.gid;(注意这边的变化)

end$

创建完毕。

再执行delete from o where oid = 2$

会发现商品2的数量又变为10了。

 

第二种情况:

监视地点:o表

监视事件:update

触发时间:after

触发事件:update

对于update而言:被修改的行,修改前的数据,用old来表示,old.列名引用被修改之前行中的值;

修改的后的数据,用new来表示,new.列名引用被修改之后行中的值。

那我们的触发器就该这样写:

create trigger tg4

after update on o

for each row

begin

update g set num = num+old.much-new.much where id = old/new.gid;

end$

先把旧的数量恢复再减去新的数量就是修改后的数量了。

我们来测试下:先把商品表和订单表的数据都清掉,易于测试。

假设我们往商品表插入三个商品,数量都是10,

买3个商品1:insert into o(gid,much) values(1,3)$

这时候商品1的数量变为7;

我们再修改插入的订单记录: update o set much = 5 where oid = 1$

我们变为买5个商品1,这时候再查询商品表就会发现商品1的数量只剩5了,说明我们的触发器发挥作用了。

 

假设:假设商品表有商品1,数量是10;

我们往订单表插入一条记录:

insert into o(gid,much) values(1,20);

会发现商品1的数量变为-10了。这就是问题的所在,因为我们之前创建的触发器是after,也就是说触发的语句是在插入订单记录之后才执行的,这样我们就无法判断新插入订单的购买数量。

 

先讲一下after和before的区别:

after是先完成数据的增删改,再触发,触发的语句晚于监视的增删改操作,无法影响前面的增删改动作;也就是说先插入订单记录,再更新商品的数量;

before是先完成触发,再增删改,触发的语句先于监视的增删改,我们就有机会判断,修改即将发生的操作;

 

我们用一个典型案例来区分它们的区别,新建一个触发器:

#监视地点: 商品表o

#监视事件:insert

#触发时间:before

#触发事件:update

案例:当新增一条订单记录时,判断订单的商品数量,如果数量大于10,就默认改为10

create trigger tg6

before insert on o

for each row

begin

  if new.much > 10 then

    set new.much = 10;

  end if;

  update g set num = num - new.much where id = new.gid;

end$

执行完,把之前创建的after触发器删掉,再来插入一条订单记录:

insert into o(gid,much) valus(1,20)$

执行完会发现订单记录的数量变为10,商品1的数量变为0了,就不会出现负数了。

 

触发器(trigger):监视某种情况,并触发某种操作。 触发器创建语法四要素:1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发...

1.创建触发器

以下是MariaDB中create trigger的语法:mysql不支持or replace和if not exists子句。

CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON tbl_name FOR EACH ROW
    trigger_body

触发器只能建立在永久表上,不能建立在视图和临时表上。MySQL/MariaDB中的触发器只支持行级触发器(即每行都触发一次触发器),不支持数据库级别和服务器级别的触发器。MySQL/MariaDB中的触发器虽然都是基于表的,却存储在数据库下,理解这一点很重要,以后查看、删除、引用trigger的时候都是通过数据库名称来引用的,而不是使用表来引用。

before和after是触发时间,insert/update/delete是触发事件。例如before insert表示插入记录之前触发程序。其中before触发器类似于SQL Server中的instead of触发器,作用在检查约束之前。而after触发器和SQL Server中一样,在检查约束之后才生效。

下图为SQL Server中instead of和after触发器的工作位置。在MySQL/MariaDB中是一样的,只要把MySQL/MariaDB中的概念和SQL Server中的概念对应起来即可。后文中有对该图的分析。

澳门新濠3559 1

在MySQL中,一张表只能有一个同时间、同事件的触发器,所以MySQL中不支持基于列的触发器。例如,一张表中可以存在before insert触发器和before update,所以每张表最多只能有6个触发器。但是MariaDB 10.2.3中可以为同时间、同事件创建多个触发器。

在MySQL/MariaDB中,使用old和new表分别表示触发器激活后的新旧表,在SQL Server中使用的是inserted和deleted表,其实它们的意义是等价的。但是坑爹的是MySQL/MariaDB中只能引用这两张表中的列,而无法直接引用这两张表。例如可以引用old.col_name,但是不能直接select * from old这样引用old表。

old表表示删除目标记录之后将删除的记录保存在old表中,即deleted表。new表表示向表中插入新记录之前,新记录保存在new表中,即inserted表。或者说,只要涉及了insert相关的操作就有new表,只要涉及了delete相关的操作就有old表,而update操作基本可以认为是先delete再insert的行为,所以也会触发这两张表。

注意,即使是after触发器,也是先将数据填充到old、new表中,再执行DML语句,最后激活触发器执行触发器中的语句。

在下面的小节中会分别验证不同事件不同时间的触发器行为。在验证它们之前,先创建示例数据。

CREATE DATABASE IF NOT EXISTS test ;

USE test ;

CREATE OR REPLACE TABLE emp (
    emp_no INT (11) NOT NULL,
    mgr_no INT (11) DEFAULT NULL,
    emp_name VARCHAR (30) DEFAULT NULL,
    PRIMARY KEY (emp_no)
) 
INSERT INTO emp (emp_no, mgr_no, emp_name) VALUES
    (1, NULL, 'David'),
    (2, 3, 'Mariah'),
    (3, 1, 'Tommy'),
    (4, 1, 'Jim'),
    (5, 3, 'Selina'),
    (6, 4, 'John'),
    (8, 3, 'Monty');

查看该表数据。

澳门新濠3559 2

再创建一个极其简单的审核表audit,该表前两列为自增列和注释列,后面的列结构等同于emp表。

DROP TABLE IF EXISTS  audit;
CREATE TABLE audit AS SELECT * FROM emp WHERE 1=0;
ALTER TABLE audit ADD id INT AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE audit ADD note CHAR(50) AFTER id;

CREATE TRIGGER <触发器名称>  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
ON <表名称>  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW  --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
<触发器SQL语句>  --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。

2.insert触发器

insert触发器的作用是:当向表中插入数据的时候,将会激活触发器。有两类:before和after触发器,分别表示数据插入到表中之前和数据插入到表中之后激活触发器。

注意,只要向表中插入了新行,就会激活insert触发器。插入新行的动作不仅仅只有insert语句,还有其他插入操作,例如load data语句、replace语句等等。

# 创建before insert触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo1 
    BEFORE INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,'before insert',new.emp_no,new.mgr_no,new.emp_name);
    END$$
DELIMITER ;

# 创建after insert触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo2
    AFTER INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,'after insert',new.emp_no,new.mgr_no,new.emp_name); 
    END$$
DELIMITER ;

before insert触发器的作用是:当向表emp中insert数据时,将首先激活该触发器,该触发器首先会将待插入数据填充到new表中,再向审核表audit中插入一行数据,并标明此次触发操作是"before insert"。触发器执行结束后,才开始向emp表中插入数据。

after insert触发器的作用是:当向表emp中insert数据时,将先将数据填充到new表中,再插入到emp表,之后激活该触发器,该触发器会向审核表audit中插入一行数据,并标明此次触发操作是"after insert"。

现在向emp表中插入数据进行测试。

INSERT INTO emp VALUES(10,3,'longshuai');

插入之后,查看audit表。

MariaDB [test]> select * from audit;
+----+---------------+--------+--------+-----------+
| id | note          | emp_no | mgr_no | emp_name  |
+----+---------------+--------+--------+-----------+
|  1 | before insert |     10 |      3 | longshuai |
|  2 | after insert  |     10 |      3 | longshuai |
+----+---------------+--------+--------+-----------+

可以看到,一次insert操作触发了before insert和after insert两个触发器。且无论是before还是after insert触发器都有new表的存在。

在mariadb 10.2.3版本之后,一个表中可以为同一时间、同一事件创建多个触发器(在mysql中不允许)。例如:

# 创建第二个after insert触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo3
    AFTER INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,'after insert2',new.emp_no,new.mgr_no,new.emp_name); 
    END$$
DELIMITER ;

show triggers;

澳门新濠3559 3

此处删除新建的这个trigger,注意删除trigger的时候是通过数据库名称来也引用trigger的,而不是table名称。

drop trigger test.trig_demo3;

顾名思义,new是新插入的数据,old是原来的数据

3.delete触发器

delete触发器的作用是:当删除表中数据记录的时候,将会激活触发器。

有两类insert触发器:before和after触发器,分别表示表中记录被删除之前和表中数据被删除之后激活触发器。

注意,delete触发器只在表中记录被删除的时候才会被激活。例如delete语句、replace语句。但是drop语句、truncate语句不会激活delete触发器,因为它们是DDL语句,而MySQL/MariaDB不支持DDL触发器,它们并没有对表中的记录执行delete操作。

# 创建before delete触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo3 
    BEFORE DELETE ON test.emp FOR EACH ROW 
    BEGIN
        INSERT INTO audit VALUES(NULL,'before delete',old.emp_no,old.mgr_no,old.emp_name); 
    END$$
DELIMITER ;

# 创建after delete触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo4
    AFTER DELETE ON test.emp FOR EACH ROW 
    BEGIN
        INSERT INTO audit VALUES(NULL,'after delete',old.emp_no,old.mgr_no,old.emp_name); 
    END$$
DELIMITER ;

这两个delete事件的触发器作用很简单,先将待删除的记录插入到old表中,再在删除表中的记录之前、之后,向审核表audit中插入一行'before delete'或'after delete'的审核日志。

现在删除emp表中的一行记录进行测试。

delete from emp where emp_no=10;

删除emp表中数据之后,查看audit表。

MariaDB [test]> SELECT * FROM audit;
+----+---------------+--------+--------+-----------+
| id | note          | emp_no | mgr_no | emp_name  |
+----+---------------+--------+--------+-----------+
|  1 | before insert |     10 |      3 | longshuai |
|  2 | after insert  |     10 |      3 | longshuai |
|  3 | before delete |      0 |   NULL | NULL      |
|  4 | after delete  |      0 |   NULL | NULL      |
+----+---------------+--------+--------+-----------+

可见,一次delete操作触发了before delete和after delete触发器。且删除记录前后old表都存在。

insert只会有new,代表着要插入的新记录

4.update触发器

update触发器的作用是:当表中数据记录被修改的时候,将会激活触发器。

有两类update触发器:before和after触发器,分别表示表中记录被修改之前和表中数据被修改之后激活触发器。

注意,update操作可以认为是先delete再insert,因此它将填充old表和new表。

# 创建before update触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo5
    BEFORE UPDATE ON test.emp FOR EACH ROW 
    BEGIN
        INSERT INTO audit VALUES(NULL,'before update from new',new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,'before update from old',old.emp_no,old.mgr_no,old.emp_name);
    END$$
DELIMITER ; 

# 创建after update触发器
DELIMITER $$   
CREATE OR REPLACE TRIGGER test.trig_demo6
    AFTER UPDATE ON test.emp FOR EACH ROW 
    BEGIN
        INSERT INTO audit VALUES(NULL,'after update from new',new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,'after update from old',old.emp_no,old.mgr_no,old.emp_name); 
    END$$
DELIMITER ;

before update触发器的作用是:当更新emp表中的一条记录时,首先将表中该行记录插入到old表中,待更新结果插入到new表中,然后激活触发器,向审核表中写入数据,最后修改emp表中的记录。
after update触发器的作用是:当更新emp表中的一条记录时,首先将表中该行记录插入到old表中,待更新结果插入到new表中,然后修改emp表中的记录,最后激活触发器,向审核表中写入数据。

更新emp表中一行记录。

update emp set emp_no=7 where emp_no=8;

查看audit表。

MariaDB [test]> select * from audit;
+----+------------------------+--------+--------+-----------+
| id | note                   | emp_no | mgr_no | emp_name  |
+----+------------------------+--------+--------+-----------+
|  1 | before insert          |     10 |      3 | longshuai |
|  2 | after insert           |     10 |      3 | longshuai |
|  3 | before delete          |      0 |   NULL | NULL      |
|  4 | after delete           |      0 |   NULL | NULL      |
|  5 | before update from new |      7 |      3 | Monty     |
|  6 | before update from old |      8 |      3 | Monty     |
|  7 | after update from new  |      7 |      3 | Monty     |
|  8 | after update from old  |      8 |      3 | Monty     |
+----+------------------------+--------+--------+-----------+

可以看到,一次update操作触发了before update触发器和after update触发器,并且update操作时,new和old两张表中都有新旧数据。上面的结果中from new对应的是更新后的数据,来源于更新前填充的new表,from old对应的是更新前的旧数据,来源于更新前填充的old表。

delete只会有old,代表着要删除的记录

5.通过on duplicate key update分析触发器触发原理

在MySQL/MariaDB中,如果向表中插入的数据有重复冲突检测时会阻止插入。解决这个问题的其中一个方法就是使用on duplicate key update子句。这个子句应用在insert字句中,但其中涉及到了update操作,那到底会触发哪些触发器呢?

这里先清空上面的audit表。

TRUNCATE audit;

首先测试下使用on duplicate key update子句插入无重复的记录。注意,emp表的emp_no列具有主键属性,它不允许出现重复值。

INSERT INTO emp VALUES(15,5,'xiaofang') ON DUPLICATE KEY UPDATE emp_name='xiaofang';

查看audit表。

MariaDB [test]> select * from audit;
+----+---------------+--------+--------+----------+
| id | note          | emp_no | mgr_no | emp_name |
+----+---------------+--------+--------+----------+
|  1 | before insert |     15 |      5 | xiaofang |
|  2 | after insert  |     15 |      5 | xiaofang |
+----+---------------+--------+--------+----------+

可以看到,在插入没有重复冲突的行只触发了before insert和after insert触发器。没有触发update触发器。

再插入一条有重复冲突的记录。

TRUNCATE audit;
INSERT INTO emp VALUES(3,1,'xiaofang') ON DUPLICATE KEY UPDATE emp_name='xiaofang';

查看audit表:

MariaDB [test]> select * from audit;
+----+------------------------+--------+--------+----------+
| id | note                   | emp_no | mgr_no | emp_name |
+----+------------------------+--------+--------+----------+
|  1 | before insert          |      3 |      1 | xiaofang |
|  2 | before update from new |      3 |      1 | xiaofang |
|  3 | before update from old |      3 |      1 | Tommy    |
|  4 | after update from new  |      3 |      1 | xiaofang |
|  5 | after update from old  |      3 |      1 | Tommy    |
+----+------------------------+--------+--------+----------+

可以看到,这里触发了3个触发器:before insert/before update/after update,为什么前面只触发了两个insert触发器而这里触发了3个触发器。其实根据下面的图很好分析。

澳门新濠3559 4

insert into... on duplicate key update语句中,插入没有重复值冲突的记录时,首先判断是否存在before insert触发器,有就触发,触发之后检查约束,发现没有重复值冲突,然后直接触发after insert触发器。所以这种情况下只触发了before insert和after insert触发器。

而插入有重复值冲突的记录时,首先触发了before insert触发器,然后检查约束发现存在重复值冲突,所以改insert操作为update操作,update操作再次回到事务的顶端,先触发before update再检查约束,这时候已经不再重复值冲突,所以后面触发after update触发器。

update由于执行的是先删除旧的记录,再插入新的记录,因此new和old都会有,且含义与上面的相同

6.replace into算法验证

插入新记录时,对于重复值冲突的记录,使用replace into语句代替insert into是另一种方法。这种方法实现方式和on duplicate key update方式不一样。

replace into算法说明如下:

  1. 尝试插入新行。
  2. 存在重复值冲突时,从表中删除重复行。
  3. 将新行插入到表中。

也就是说,存在重复值冲突时,如果使用触发器的话,将先触发before insert,再触发delete操作,先是before delete再是after delete,最后触发after insert。

以下是验证过程和结果:首先清空audit表,再插入重复冲突的记录。

TRUNCATE audit;
REPLACE INTO emp VALUES(3,1,'gaoxiaofang');

查看audit表:

MariaDB [test]> select * from audit;
+----+---------------+--------+--------+-------------+
| id | note          | emp_no | mgr_no | emp_name    |
+----+---------------+--------+--------+-------------+
|  1 | before insert |      3 |      1 | gaoxiaofang |
|  2 | before delete |      0 |   NULL | NULL        |
|  3 | after delete  |      0 |   NULL | NULL        |
|  4 | after insert  |      3 |      1 | gaoxiaofang |
+----+---------------+--------+--------+-------------+

显然,和算法说明的结果是对应的。

根据ID做一个hash路由算法分配服务器

7.查看、删除触发器

mysql> SHOW CREATE TRIGGER trig_demo5G
*************************** 1. row ***************************
               Trigger: trig_demo5
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`root`@`192.168.100.%` TRIGGER `test`.`trig_demo5` BEFORE UPDATE ON `test`.`emp`
    FOR EACH ROW BEGIN
        INSERT INTO audit VALUES(NULL,'before update from new',new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,'before update from old',old.emp_no,old.mgr_no,old.emp_name);
    END
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci

mysql> show triggers;
mysql> show trigger like 'pattern';
mysql> show trigger where 'expression';

但是要注意,这个like的模式是对表名进行匹配的,而不是触发器名。例如触发器trig_demo1是基于emp表创建的,则使用like 'emp'而不能使用like 'trig_demo1'。

在information_schema中有TRIGGERS元数据表:

例如:

mysql> select * from information_schema.triggers where trigger_name='trig_demo1'G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: trig_demo1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: emp
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
        INSERT INTO audit VALUES(null,'before insert',NEW.emp_no,new.mgr_no,new.emp_name);
    END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: 
                   DEFINER: root@192.168.100.%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

删除触发器的时候,需要使用drop语句指定数据库名,而不是指定表名称。例如:

DROP TRIGGER [ IF EXISTS ] test.example_trigger;

触发器更新本表

Can’t update table ‘tbl’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger 错误,
如果你在触发器里面对刚刚插入的数据进行了insert/update, 则出现这个问题。因为会造成循环的调用.
应该使用set操作,而不是在触发器里使用update,比如

Drop trigger route ;//删除触发器

delimiter $(意思是告诉mysql语句的结尾换成以$结束)

delimiter $
create trigger route  
before insert on arcticle_link
for each row
BEGIN
      set NEW.server_id=(floor(rand()*100)+1);
END$

rand的范围0 <= rand() < 1.0

error---Updating of NEW row is not allowed in after trigger
对new赋值的时候只能在触发器before中只用,在after中是不能使用的,比如
在after的触发器中,new的赋值已经结束了,只能读取内容。 如果使用after不能使用new赋值,只能取值
new在before触发器中赋值,取值;在after触发器中取值

编辑:数据库 本文来源:触发器用来实现在永久表上进行某些操作时触发

关键词: