您的当前位置:首页正文

MySQL触发器的基本使用

2020-11-09 来源:爱够旅游网

一、Mysql触发器的基本使用 ① 创建触发器 创建触发器语法如下: view plain copy CREATE TRIGGER trigger_nametrigger_timetrigger_event ON tbl_name FOR EACHROWtrigger_stmt ■ trigger_name:标识触发器名称,用户自行指定; ■ trigger_time:标识触发

一、Mysql触发器的基本使用

① 创建触发器

创建触发器语法如下:

view plain copy

  1. CREATE TRIGGER trigger_name trigger_time trigger_event
  2. ON tbl_name FOR EACH ROW trigger_stmt

■ trigger_name:标识触发器名称,用户自行指定;

■ trigger_time:标识触发时机,用before和after替换;

■ trigger_event:标识触发事件,用insert,update和delete替换;

■ tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;

■ trigger_stmt:触发器程序体。

触发器程序可以使用begin和end作为开始和结束,中间包含多条语句。下面给出一个触发器实例:

view plain copy

  1. CREATE TRIGGER trig_useracct_update
  2. AFTER UPDATE
  3. ON SF_User.useracct FOR EACH ROW
  4. BEGIN
  5. IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN
  6. IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN
  7. IF NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN
  8. INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
  9. END IF;
  10. ELSE
  11. INSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
  12. END IF;
  13. END IF;
  14. END;

上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列,在上述实例中,OLD.ulevelid表示表 SF_User.useracct修改之前ulevelid列的值,NEW.ulevelid表示表SF_User.useracct修改之后 ulevelid列的值。另外,如果是insert型触发器,NEW.ulevelid也表示表SF_User.useracct新增行的ulevelid列值;如果是delete型触发器OLD.ulevelid也表示表SF_User.useracct删除行的ulevelid列原值。

另外,OLD列是只读的,NEW列则可以在触发器程序中再次赋值。

上述实例也使用了IF,THEN ,ELSE,END IF等关键字。在触发器程序体中,在beigin和end之间,可以使用顺序,判断,循环等语句,实现一般程序需要的逻辑功能。

② 查看触发器

查看触发器语法如下,如果知道触发器所在数据库,以及触发器名称等具体信息:

view plain copy

  1. SHOW TRIGGERS from SF_User like "usermaps%";
  2. /* 查看SF_User库上名称和usermaps%匹配的触发器 */

如果不了解触发器的具体的信息,或者需要查看数据库上所有触发器,如下:

view plain copy

  1. SHOW TRIGGERS;
  2. //查看所有触发器

用上述方式查看触发器可以看到数据库的所有触发器,不过如果一个库上的触发器太多,由于会刷屏,可能没有办法查看所有触发器程序。这时,可以采用如下方式:

MySQL中有一个information_schema.TRIGGERS表,存储所有库中的所有触发器,desc information_schema.TRIGGERS,可以看到表结构:

view plain copy

  1. +----------------------------+--------------+------+-----+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +----------------------------+--------------+------+-----+---------+-------+
  4. | TRIGGER_CATALOG | varchar(512) | YES | | NULL | |
  5. | TRIGGER_SCHEMA | varchar(64) | NO | | | |
  6. | TRIGGER_NAME | varchar(64) | NO | | | |
  7. | EVENT_MANIPULATION | varchar(6) | NO | | | |
  8. | EVENT_OBJECT_CATALOG | varchar(512) | YES | | NULL | |
  9. | EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |
  10. | EVENT_OBJECT_TABLE | varchar(64) | NO | | | |
  11. | ACTION_ORDER | bigint(4) | NO | | 0 | |
  12. | ACTION_CONDITION | longtext | YES | | NULL | |
  13. | ACTION_STATEMENT | longtext | NO | | | |
  14. | ACTION_ORIENTATION | varchar(9) | NO | | | |
  15. | ACTION_TIMING | varchar(6) | NO | | | |
  16. | ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | |
  17. | ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | |
  18. | ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | |
  19. | ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | |
  20. | CREATED | datetime | YES | | NULL | |
  21. | SQL_MODE | longtext | NO | | | |
  22. | DEFINER | longtext | NO | | | |
  23. +----------------------------+--------------+------+-----+---------+-------+

这样,用户就可以按照自己的需要,查看触发器,比如使用如下语句查看上述触发器:

view plain copy

  1. select * from information_schema. TRIGGERS where TRIGGER_NAME= 'trig_useracct_update';

③ 删除触发器

删除触发器语法如下:

view plain copy

  1. DROP TRIGGER [schema_name.]trigger_name

二、MySQL触发器的trigger_time和trigger_event

现在,重新注意到trigger_time和trigger_event,上文说过, trigger_time可以用before和after替换,表示触发器程序的执行在sql执行的前还是后;trigger_event可以用 insert,update,delete替换,表示触发器程序在什么类型的sql下会被触发。

在一个表上最多建立6个触发器,即① before insert型;② before update型;③ before delete型;④ after insert型;⑤ after update型;⑥ after delete型。

触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”(摘自MySQL使用手册)。

另外还有一点需要注意,MySQL除了对insert,update,delete基本操作进行定义外,还定义了load data和replace语句,而load data和replace语句也能引起上述6中类型的触发器的触发。

Load data语句用于将一个文件装入到一个数据表中,相当与一系列insert操作。replace语句一般来说和insert语句很像,只是在表中有 primary key和unique索引时,如果插入的数据和原来primary key和unique索引一致时,会先删除原来的数据,然后增加一条新数据;也就是说,一条replace sql有时候等价于一条insert sql,有时候等价于一条delete sql加上一条insert sql。即是:

■ Insert型触发器:可能通过insert语句,load data语句,replace语句触发;

■ Update型触发器:可能通过update语句触发;

■ Delete型触发器:可能通过delete语句,replace语句触发;

显示全文