MySQL的断言和触发器

环~

于 2024-07-01 13:00:47 发布

阅读量56

点赞数 1

文章标签： mysql 数据库

MySQL的断言和触发器

一、断言
- 1、创建断言
- 2、删除断言
二、触发器

一、断言

在SQL中可以使用数据定义语言中的 CREATE ASSERTION 语句，通过声明性断言（declarative assertions）来定义更具一般性的约束。可以定义涉及多个表或聚集函数操作的比较复杂的完整性约束。断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使用断言不为真值的操作都会被拒绝。

1、创建断言

SQL语法格式

CREATE ASSERTION <断言名> <CHECK 子句>

每个断言都被赋予了一个名字，<CHECK 子句>中的约束条件与 WHERE 子句的条件表达类似。
[例1] 限制数据库课程表最多50名学生选修。

CREATE ASSERTION ASSE_SC_DB_NUM 
	CHECK 
	( 
	50 >= (
			select count(*) from Course,SC 
			where SC.cno=course.cno AND course.Cname = '数据库'
		)
	);

2、删除断言

SQL语法格式

DROP ASSERTION <断言名>

注意：

MySQL8.0.26不支持断言，而Oracle、PostgreSQL、IBM Db2 支持断言。

二、触发器

触发器(trigger)是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义触发器将被保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

1、创建部门表和老师表

//部门表
CREATE TABLE dept(
    Deptno numeric(2), 				/*部门编号*/
    Dname char(9) UNIQUE NOT NULL,  /*部门名称*/
    Location char(10),				/*部门所在位置*/
    primary key (Deptno)			/*定义部门编号为主键*/
);

//numeric(p,s)
//p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。
//s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。
//老师表，要求每个老师应发工资不低于3000元。
//应发工资是工资列 Sal 与扣除项 Deduct 之和
CREATE TABLE teacher(
    Tno numeric(4) primary key,		/*老师编号*/
    Tname char(10), 				/*老师姓名*/
    Job char(8), 					/*工作内容*/
    Sal numeric(7,2),				/*老师工资*/
    Deduct numeric(7,2),			/*老师的扣除项*/
    Deptno numeric(2),				/*老师所在部门*/
    constraint teacherKey foreign key (Deptno)
    references dept (Deptno), 		/*定义外键约束为teacherKey,参考部门表的Deptno*/
    constraint C1 check (Sal + Deduct >= 3000) /*定义工资不低于3000约束为C1*/
);

部门表

Deptno	Dname	Location
1	教务处	01A302
2	后勤处	03A405
3	党支部	06A404
4	团支部	06A501

插入部门数据的SQL语句如下：

insert into dept values
(1,'教务处','01A302'),
(2,'后勤处','03A405'),
(3,'党支部','06A404'),
(4,'团支部','06A501');

老师表

Tno	Tname	Job	Sal	Deduct	Deptno
1	梁伟	教授语文	3500	-500	1
2	郭岩	教授数学	3500	-200	1
3	李玉英	教授英语	3500	-100	1
4	张健	设备管理	3200	-50	2
5	郑静	卫生管理	3200	0	2
6	牛建军	政治学习	4000	-200	3
7	刘斌	党员管理	3800	0	3
8	汪云	活动开展	3500	-20	4
9	张建平	团员学习	3500	0	4
10	郭娟	团员管理	3200	0	4

插入老师数据的SQL语句如下：

insert into teacher values
(1,'梁伟','教授语文',3500,-500,1),
(2,'郭岩','教授数学',3500,-200,1),
(3,'李玉英','教授英语',3500,-100,1),
(4,'张健','设备管理',3200,-50,2),
(5,'郑静','卫生管理',3200,0,2),
(6,'牛建军','政治学习',4000,-200,3),
(7,'刘斌','党员管理',3800,0,3),
(8,'汪云','活动开展',3500,-20,4),
(9,'张建平','团员学习',3500,0,4),
(10,'郭娟','团员管理',3200,0,4);

2、定义触发器

触发器又叫做事件-条件-动作(event-condition-action)规则。当特定的系统事件（如对一个表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。
SQL使用CREATE TRIGGER命令建立触发器，其一般格式为

 DELIMITER //   /*修改mysql的结束符，将原来的分号(;)结束符修改为(//)*/
 CREATE TRIGGER <触发器名>         	 /*每当触发事件发生时，该触发器被激活*/
 {BEFORE|AFTER} <触发事件> ON <表名>  /*指明触发器激活的时间是在执行触发事件前或后*/
 FOR EACH{ROW | STATEMENT}        	 /*定义触发器的类型，指明动作体执行的频率*/
 BEGIN 
 	<trigger action body(触发动作体)>      	 /*仅当触发条件为真时才执行触发动作体*/
 END// 
 DELIMITER ;  /*改回结束符*/

下面对定义触发器的各部分语法进行详细说明。

（1）只有表的拥有者，即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。

（2）触发器名
触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的并且触发器名和表名必须在同一模式下。
（3）表名
触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器，因此该表也称为触发器的目标表。
（4）触发事件
触发事件可以是INSERT、DELETE 或UPDATE,也可以是这几个事件的组合，如一旦定义，INSERT OR DELETE等，还可以是UPDATE OF <触发列，…>，即进一步指明修改哪些列时激活触发器。AFTER / BEFORE 是触发的时机。AFTER表示在触发事件的操作执行之后激活触发器；BEFORE表示在触发事件的操作执行之前激活触发器。

（5）触发器类型
触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）。
例如，假设在teacher表上创建一个 after update 触发器，触发事件是update 语句：

UPDATE teacher SET Deptno=4;

假设表里面有500行，如果定义语句级触发器，那么执行完 UPDATE 语句后触发动作体执行一次；若定义为行级语句级触发器，触发动作体将执行 500 次。

（6）触发动作体
触发动作体既可以是一个匿名 PL/SQL 过程块，也可以是对已创建存储过程的调用。如果是行级触发器，用户可以在过程体中使用 new 和 old 引用 UPDATE/INSERT 事件后的新值和 UPDATE/DELETE 事件之前的旧值；如果是语句级触发器，则不能在触发动作体中使用 new 或 old 进行引用。
如果触发动作体执行失败，激活触发器的事件（即对数据库的增、删、改操作）就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

注意：

MySQL 8.0 支持的是行级触发器，而不是语句级触发器。
Oracle数据库和SQL Server支持语句级触发器

[例1] 当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U（Sno、Cno、Oldgrade、Newgrade）中，其中Oldgrade 是修改前的分数，Newgrade 是修改后的分数。

//创建一个简单的SC_U表
CREATE TABLE SC_U (
    Sno CHAR(9),
    Cno CHAR(4),
    Oldgrade SMALLINT,
    Newgrade SMALLINT
);
//创建触发器
DELIMITER //

CREATE TRIGGER SC_T  /*SC_T是触发器的名字*/
AFTER UPDATE ON SC   /*UPDATE ON SC是触发事件*/
FOR EACH ROW   /*行级触发器。即每执行一次Grade的更新，下面规则就执行一次*/
BEGIN
    IF New.Grade >= 1.1 * Old.Grade THEN  /*触发条件，只有条件为真时才执行*/
        INSERT INTO SC_U(Sno, Cno, Oldgrade, Newgrade)  
        VALUES(Old.Sno, Old.Cno, Old.Grade, New.Grade);
    END IF;
END//insert into Student values

DELIMITER ;

//测试触发器
1、先查看SC_T表中是否有数据，并查看Student表的学号（Sno）
SELECT * FROM Student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 202015121 | 张三   | 男   |   20 | CS    |
| 202015122 | 李四   | 男   |   19 | CS    |
| 202015123 | 王敏   | 女   |   21 | MS    |
| 202015124 | 刘晨   | 女   |   20 | IS    |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

2、在SC表中插入一条学号（Sno）在Student表中的数据进行测试
INSERT INTO SC VALUES("202015124",1,50);
3、更新这条数据，把分数（Grade）增加10%
UPDATE SC SET Grade=70 where Sno = "202015124";
4、查看SC_T表中是否存在数据
SELECT * FROM SC_U WHERE Sno = "202015124";
+-----------+------+----------+----------+
| Sno       | Cno  | Oldgrade | Newgrade |
+-----------+------+----------+----------+
| 202015124 | 1    |       50 |       70 |
+-----------+------+----------+----------+
1 row in set (0.00 sec)

[例2] 使用语句级触发器将每次对表 Student 的插入操作所增加的学生个数记录到表 Student_InsertLog 中。

1、创建一个简单的 Student_InsertLog 表
CREATE TABLE Student_InsertLog(
	InsertId INT NOT NULL AUTO_INCREMENT,
	Number numeric(8),
	Insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (InsertId)
);

2、创建触发器，触发器的名字为 Student_Count（该触发器在mysql8.0创建不成功）
DELIMITER //

CREATE TRIGGER Student_Count 
AFTER INSERT ON Student 
FOR EACH STATEMENT 
BEGIN 
	INSERT INTO Student_InsertLog(Number) SELECT ROW_COUNT(); 
END//

DELIMITER ;

3、插入单条语句和插入多条语句，测试触发器
INSERT INTO Student VALUES(202015125,'廖娜','女',20,'IS');
INSERT INTO Student VALUES
(202015126,'窦红梅','女',20,'IT'),
(202015127,'聂想','男',21,'SE'),
(202015128,'陈阳','男',19,'IS');

4、查看Student_InsertLog表
SELECT * FROM Student_InsertLog;

在MySQL 8.0中，虽然不能创建传统意义上的语句级触发器，但可以通过一些技巧来模拟语句级触发器的行为。以下是一个实现该功能的方法：

1、创建Student_InsertLog 表
2、创建一个变量来存储插入的行数：在MySQL中，你可以使用用户定义的变量来存储插入操作影响的行数。
3、创建一个触发器来更新这个变量：创建一个AFTER INSERT触发器，每次插入操作后更新变量的值。
SET @insert_count = 0; -- 设置变量为0
DELIMITER //

CREATE TRIGGER Student_Count 
AFTER INSERT ON Student 
FOR EACH ROW
BEGIN
    SET @insert_count = @insert_count + 1; 
END//

DELIMITER ;

4、在插入操作后，将变量的值插入到 Student_InsertLog 表：每次执行插入操作后，你需要手动将@insert_count变量的值插入到Student_InsertLog表中。这可以通过一个存储过程来实现，或者在应用程序中处理。

DELIMITER //  

CREATE PROCEDURE LogStudentInsert() 
BEGIN
           -- 插入值到Student_InsertLog表
	INSERT INTO Student_InsertLog (Number) VALUES(@insert_count);
           -- 重置全局变量或会话变量
	SET @insert_count = 0; 
END//

DELIMITER ;

//查看存储过程，将your_database_name换成自己的数据库名
SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

5、每次插入操作后调用存储过程：在你的应用程序中，每次执行插入操作后，调用LogStudentInsert存储过程。
//调用存储过程
CALL LogStudentInsert();

6、插入数据进行测试
//插入单条数据测试
mysql> INSERT INTO Student VALUES(202015125,'廖娜','女',20,'IS');
//调用存储过程
mysql> CALL LogStudentInsert();
//查看结果
mysql> SELECT * FROM Student_InsertLog;
+----------+--------+---------------------+
| InsertId | Number | Insert_time         |
+----------+--------+---------------------+
|        1 |      1 | 2024-07-01 11:17:28 |
+----------+--------+---------------------+
//插入多条数据进行测试
mysql> INSERT INTO Student VALUES
(202015126,'窦红梅','女',20,'IT'),
(202015127,'聂想','男',21,'SE'),
(202015128,'陈阳','男',19,'IS');
mysql> CALL LogStudentInsert();
mysql> SELECT * FROM Student_InsertLog;
+----------+--------+---------------------+
| InsertId | Number | Insert_time         |
+----------+--------+---------------------+
|        1 |      1 | 2024-07-01 11:17:28 |
|        2 |      3 | 2024-07-01 11:18:08 |
+----------+--------+---------------------+

这种方法的缺点是需要在每次插入操作后手动调用存储过程来记录日志，这增加了应用程序的复杂性。但是，由于MySQL 8.0不支持语句级触发器，这是模拟该功能的一种可行方法。

请注意，这种方法并不是完全的语句级触发器，因为它需要在每次插入操作后手动调用存储过程，但它可以模拟在插入操作后记录插入个数的行为。

[例3] 定义一个before行级触发器，为教师表Teacher定义完整性规则“教授化学”的工资不得低于4000元，如果低于4000元，自动改为4000元。

注意：MySQL8.0中的触发器设计为对单个数据库操作事件（如INSERT、UPDATE或DELETE）进行响应。
DELIMITER //

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON teacher   /*触发事件是插入或更新操作*/
FOR EACH ROW        /*行级触发器*/
BEGIN                    /*定义触发动作体，是PL/SQL过程块*/
	IF (new.Job = '教授化学') AND (new.Sal < 4000) THEN
		SET  new.Sal = 4000;
	END IF;
END//
DELIMITER ;

优化上面触发器并实现，将一个触发器拆分成两个触发器
1、插入触发器
DELIMITER //

CREATE TRIGGER Insert_Sal 
BEFORE INSERT ON teacher   /*触发事件是插入或更新操作*/
FOR EACH ROW        /*行级触发器*/
BEGIN                    /*定义触发动作体，是PL/SQL过程块*/
	IF (new.Job = '教授化学') AND (new.Sal < 4000) THEN
		SET new.Sal = 4000;
	END IF;
END//

DELIMITER ;

2、更新触发器
DELIMITER //

CREATE TRIGGER Update_Sal 
BEFORE UPDATE ON teacher   /*触发事件是插入或更新操作*/
FOR EACH ROW        /*行级触发器*/
BEGIN                    /*定义触发动作体，是PL/SQL过程块*/
	IF (new.Job = '教授化学') AND (new.Sal < 4000) THEN
		SET new.Sal = 4000;
	END IF;
END//

DELIMITER ;

3、测试进行插入和更新数据
//插入测试
mysql> INSERT INTO Teacher VALUES(11,'刘倩','教授化学',3500,-500,1);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM teacher where Job="教授化学";
+-----+--------+--------------+---------+---------+--------+
| Tno | Tname  | Job          | Sal     | Deduct  | Deptno |
+-----+--------+--------------+---------+---------+--------+
|  11 | 刘倩   | 教授化学     | 4000.00 | -500.00 |      1 |
+-----+--------+--------------+---------+---------+--------+
1 row in set (0.00 sec)
//更新测试
mysql> UPDATE teacher SET Sal='3000' WHERE Job="教授化学";
Query OK, 0 rows affected (0.04 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM teacher where Job="教授化学";
+-----+--------+--------------+---------+---------+--------+
| Tno | Tname  | Job          | Sal     | Deduct  | Deptno |
+-----+--------+--------------+---------+---------+--------+
|  11 | 刘倩   | 教授化学     | 4000.00 | -500.00 |      1 |
+-----+--------+--------------+---------+---------+--------+
1 row in set (0.00 sec)

3、激活触器

触发器的执行是由触发事件激活，并由数据库服务器自动执行的。一个数据表上可能
定义了多个触发器，如多个 BEFORE 触发器、多个 AFTER 触发器等，同一个表上的多个
触发器激活时遵循如下的执行顺序：

执行该表上的BEFORE触发器。
激活触发器的SQL语句。
执行该表上的AFTER触发器。

对于同一个表上的多个BEFORE（AFTER）触发器，遵循“谁先创建谁先执行”的原则，即按照触发器创建的时间先后顺序执行。

4、删除触发器

删除触发器的SQL语法如下：

 DROP  TRIGGER <触发器名> ON <表名>

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。
触发器是一种功能强大的工具，但在使用时要慎重，因为在每次访问一个表时都可触发一个触发器，这样会影响系统的性能

环~

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL的断言和触发器

MySQL的断言和触发器
复制链接

扫一扫