开发基础系列19

  触发器是与表有关的数据库对象,触发器只好是针对性创设的恒久表,而不可能是不经常表。

风流洒脱、创制触发器

触发器是与表有关的数据库对象,在知足定义法则时接触,并执行触发器中定义的话语集结

触发器用来实今后恒久表上进行一些操作时接触运维另后生可畏操作。

1.1 创立触发器

触发器是与表有关的数据库对象,在满足定义法则时接触,并推行触发器中定义的讲话集合。能够支持应用在数据库端确定保证数量的完整性。

始建触发器

开创触发器的语法如下:

create trigger trigger_name
trigger_time trigger_event on tbl_name 
FOR EACH ROW 
trigger_stmt

小心:触发器只好创建在永世表(Permanent Table)上,不可能对有的时候表(Temporary Table)创造触发器

其中 trigger_time 是触发器的触及时间,能够是 before 可能 after,before 的含义指在自小编钻探限定前触发,而 after 是在检讨约束后触发

trigger_event 正是触发器的触及事件,能够是 insert,update 可能delete

对同一个表相近触发时间的相近触发事件,只可以定义二个触发器

运用小名 oldnew 来援引触发器中产生变化的记录内容

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中的概念对应起来就能够。后文中有对该图的分析。

星彩彩票app下载 1

在MySQL中,一张表只可以有一个同临时候间、同事件的触发器,所以MySQL中不帮衬基于列的触发器。比方,一张表中得以存在before insert触发器和before update,所以每张表最三只可以有6个触发器。可是MariaDB 10.2.3中可以为同临时间间、同事件创制三个触发器。

在MySQL/MariaDB中,使用old和new表独家代表触发器激活后的新旧表,在SQL Server中使用的是inserted和deleted表,其实它们的含义是等价的。不过坑爹的是MySQL/玛丽亚DB中只可以援引这两张表中的列,而没有任何进展直接引用这两张表。比方能够援引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');

查阅该表数据。

星彩彩票app下载 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 trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

表中的有个别数据变动,同不经常间唤起别的连锁数据变动时,使用触发器,是生机勃勃类特别的事务,能够监视某种数据操作(insert、update、delete),并触及相关操作(insert、update、delete);sql 1 触发 sql n

去除触发器

壹次能够去除三个触及程序,若无一些名 schema_name,默觉妥善前数据库,具体语法如下:

drop trigger [schema_name.]trigger_name

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中不容许卡塔尔(英语:State of Qatar)。举个例子:

# 创建第二个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;

星彩彩票app下载 3

此间删除新建的那几个trigger,注意删除trigger的时候是通过数据库名称来也援用trigger的,实际不是table名称。

drop trigger test.trig_demo3;

  trigger_time:是触发器的触及时间,能够是 before或after, before是反省约束前触发,而after是检查限制后触发。
  trigger_星彩彩票app下载,event:是触发器的接触事件,能够是 insert,update,delete。
  同叁个表相通的触及时间的同一触发事件,只可以定义几个触发器。使用别称old和new 来引用触发器中发生变化的笔录内容。

触发器只好创建在永远表上,不能够对临时表创立触发器。

翻看触发器

能够经过实施 show triggers 命令查看触发器的情况,语法等信息

其余多少个查看方式是查询系统表的 information_schema.triggers

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表都设有。

--  先查看city_memory数据(触发前查看表数据)
SELECT * FROM city_memory;

触发器不只能简化程序,还足以增添程序的圆滑。

触发器的行使

触发器推行的语句有以下七个约束

  • 接触程序无法调用将数据再次来到客商端的蕴藏程序,也不能够使用应用 call 语句的动态 SQL语句,不过允许存款和储蓄程序通过参数将数据重临触发程序。相当于存款和储蓄进程照旧函数通过 out 可能 inout 类型的参数将数据重返触发器是能够的,不过不能够调用直接回到数据的经过
  • 不可能在触发器中选择以显式或隐式形式开首或收尾工作的说话,如 start transaction,commit 或 rollback

MySQL 的触发器是根据 before 触发器,行操作,after 触发器的逐一实行的,此中任何一步操作产生错误都不会继续执行剩下的操作。假诺是对事务表实行的操作,那么会全部作为叁个工作被回滚(Rollback),可是生机勃勃旦是对非事务表实行的操作,那么早已更新的记录将不能够回滚,这也是陈设触发器的时候须要小心的题目

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表。

星彩彩票app下载 4

语法四要点:

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个触发器。其实依照上面包车型地铁图很好深入分析。

星彩彩票app下载 5

insert into... on duplicate key update语句中,插入未有再度值冲突的记录时,首先决断是还是不是留存before insert触发器,有就接触,触发之后检查约束,开采未有再度值冲突,然后径直触发after insert触发器。所以这种场合下只接触了before insert和after insert触发器。

而插入有重复值冲突的记录时,首先接触了before insert触发器,然后检查节制开掘成在双重值冲突,所以改insert操作为update操作,update操作再一次归来事情的最上部,先触发before update再自己舆剧情制,那个时候已经不再重复值冲突,所以前面触发after update触发器。

-- 创建city表的insert 触发器 使用AFTER触发时间
DELIMITER $$
CREATE TRIGGER  tri_city
 AFTER INSERT ON city FOR EACH ROW BEGIN
INSERT INTO city_memory (country_id, cityname, Citycode)
VALUES (new.country_id, new.cityname, new.Citycode);     
END;
$$
DELIMITER ;

-- 插入city表数据,触发city表插入触发器
INSERT INTO  city(country_id, cityname, Citycode) VALUES(2,'中国触发','001触发');

-- 再次查看SELECT * FROM city_memory;

监视地方

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 |
 ---- --------------- -------- -------- ------------- 

猛烈,和算法验证的结果是呼应的。

星彩彩票app下载 6

监视事件(insert、update、delete)

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中有T智跑IGGE奔驰M级S元数据表:

例如:

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;

  下面是city表

接触时间(after、before)

星彩彩票app下载 7

接触事件(insert、update、delete)

2. 查看触发器

星彩彩票app下载 8

-- 使用information_schema.triggers来查看
SELECT * FROM information_schema.triggers WHERE trigger_name = 'tri_city'

CREATE TRIGGER trigger_name

星彩彩票app下载 9

  trigger_time

  1. 剔除触发器

    DROP TRIGGER tri_city;

  trigger_event

 总计:触发器的范围:

  ON tbl_name

(1)触发程序不能够调用将数据重返想客端的储存程序。

  For Each Row

(2)无法在触发器中应用以显式或隐式情势发轫或终止专门的工作的言语。

  trigger_stmt

编辑过于复杂的触发器恐怕扩展过多的触发器对记录的插入、更新、删除操作必然会有相比较严重的熏陶。不要将使用的管理逻辑过多的正视性于触发器来拍卖。

评释:触发器只好创建在永世表上

 

Trigger_time 触发器的接触时间 before 或 after

Trigger_event 触发事件 insert、update 或 delete

Trigger_stmt begin。。。end

行使别称old和new来援用触发器中发生变化的笔录内容;

对于同一表生龙活虎律触发时间同等触发事件只可以定义八个触发器

星彩彩票app下载 10

星彩彩票app下载 11

星彩彩票app下载 12

星彩彩票app下载 13

问题:如何让 update 中的 goods_number、goods_id 依照 insert 的数量实时变化?

通过 new 关键字,同理监听删除 用关键字 old

Insert 兴妖作怪为 new

星彩彩票app下载 14

Delete 从有到无为 old

星彩彩票app下载 15

Update 由 旧 变为 新 旧数据用 ord,新数据 new

星彩彩票app下载 16

函数加变量定义以致 before 和 after 的分别:

星彩彩票app下载 17

For each row 的含义:

星彩彩票app下载 18

二、删除触发器

一遍能够去除叁个触及程序;

DROP TRIGGER trigger_name;

查看触发器

SHOW TRIGGERS;

Information.Triggers表

Select * from triggers where trigger_name =”name”;

三、触发器的接受

不可能再触发器中央银行使以体现或隐式方式张开或收尾事务语句;

不能够调用将数据重临客户端的积存程序,也无法使用使用call语句的动态sql语句

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:开发基础系列19

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。