Mysql 学习笔记（五）

触发器（Trigger）

➢ 它是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ， USER_TRIGGERS 数据字典中查到。

➢ 为什么要使用触发器：

✓ 可以使用它来检查或预防坏的数据进入数据库。

✓ 可以改变或取消 INSERT、UPDATE、以及 DELETE 语句。

✓ 可以在一个会话中监视数据改变的动作。

（1）创建触发器

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <触发的 SQL 语句>

注：

✓ 触发器名称：触发器必须有名字，最多 64 个字符，可能后面会附有分隔符.它和 MySQL 中其他对象的命名方式基本相象。

✓ 触发程序的动作时间：BEFORE AFTER. 可以设置为事件发生前或后。

✓ 事件：指明了激活触发程序的语句的类型。可以是下述值之一： ✓ INSERT：将新行插入表时激活触发程序，例如，通过 INSERT、LOAD DATA 和 REPLACE 语句。 ✓ UPDATE：更改某一行时激活触发程序，例如，通过 UPDATE 语句。 ✓ DELETE：从表中删除某一行时激活触发程序，例如，通过 DELETE 和 REPLACE 语句。

✓ 表：触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器，而且必须引用永久性表，不能将触发程序与 TEMPORARY 表或视图关联起来。

✓ 触发间隔：FOR EACH ROW 通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

✓ 关于旧的和新创建的列的标识

在触发器的 SQL 语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： "NEW . column_name" 或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。对于 INSERT 语句,只有 NEW 是合法的；对于 DELETE 语句，只有 OLD 才合法；而 UPDATE 语句可以在和 NEW 以及 OLD 同时使用。下面是一个 UPDATE 中同时使用 NEW 和 OLD 的例子。

CREATE TRIGGER t21_au BEFORE UPDATE ON t22 FOR EACH ROW

BEGIN

SET @old = OLD . s1;

SET @new = NEW.s1;

END;

✓ 触发的 SQL 语句：是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用 BEGIN ... END 复合语句结构。这样，就能使用存储子程序中允许的相同语句。

例子：create trigger tri_student before insert on student for each row insert into sc(sno) value(NEW.sno);

（2）查询触发器

SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

例子：show triggers;

（3）删除触发器

DROP TRIGGER [schema_name.]trigger_name

说明：方案名称（schema_name）是可选的。如果省略了 schema（方案），将从当前方案中舍弃触发程序。DROP TRIGGER 语句需要 SUPER 权限。

例子：drop trigger tri_student;

触发器--1

触发器--2

触发器--3

触发器--4

触发器--5

（4）创建触发器例子

delimiter //

注：在命令提示符下输入delimiter // 这样是用//替换换行符，这样可避免点击换行键时执行程序。

创建两张表student(学生表)，sc(成绩表)

create table student (sno varchar(10) primary key, sname varchar(20), sage int(2), ssex varchar(5) );

create table sc ( sno varchar(10), cno varchar(10), score double, constraint pk_sc primary key (sno,cno) );

4.1）插入时触发