• 任务描述
  • 相关知识
  • 触发器的定义
  • 触发器和存储过程的区别
  • 触发器的优点
  • 触发器的作用
  • 触发器的分类
  • DML触发器
  • DDL触发器
  • 登录触发器
  • 触发器的工作原理
  • inserted表
  • deleted表
  • 创建触发器
  • 编程要求
  • 测试说明

任务描述

本关任务：通过实验，加深对数据完整性的理解，掌握创建和使用触发器。

相关知识

触发器的定义

触发器是建立在触发事件上的。例如，对表执行 INSERT 、 UPDATE 或 DELETE 等操作时， SQL Server 就会自动执行建立在这些操作上的触发器。在触发器中，包含了一系列用于定义业务规则的 SQL 语句，用来强制用户实现这些规则，从而确保数据的完整性。

触发器和存储过程的区别

触发器与存储过程的区别是运行方式的不同，触发器不能用 EXECUTE 语句调用，而是在用户执行 SQL 语句时，自动触发执行。而存储过程需要用户、应用程序或者触发器来显示地调用并执行。

触发器的优点

  • 触发器是自动的。当对表中的数据做了任何修改之后，立即被激活；
  • 触发器可以通过数据库中的相关表，进行层叠修改；
  • 触发器可以强制限制。这些限制比用 CHECK 约束所定义的更复杂，与 CHECK 约束不同的是，触发器可以引用其他表中的列。

触发器的作用

触发器的主要作用就是，其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提供比 CHECK 约束更复杂的数据完整性，并自定义错误消息。触发器的主要作用主要有以下几个方面：

  • 强制数据库间的引用完整性；
  • 级联修改数据库中所有相关的表，自动触发其它与之相关的操作；
  • 跟踪变化，撤销或回滚违法操作，防止非法修改数据；
  • 返回自定义的错误消息，约束无法返回信息，而触发器可以；
  • 触发器可以调用更多的存储过程。

触发器的分类
SQL Server 包括三种常规类型的触发器： DML 触发器、 DDL 触发器和登录触发器。

DML触发器

DML 触发器是一些附加在特定表或视图上的操作代码。当数据库服务器中，发生数据操作语言事件时，执行这些操作。 SQL Server 中的 DML 触发器有三种：

INSERT 触发器：向表中插入数据时被触发；

DELETE 触发器：从表中删除数据时被触发；

UPDATE 触发器：修改表中数据时被触发。

当遇到下列情形时，应考虑使用 DML 触发器：

  • 通过数据库中的相关表实现级联更改；
  • 防止恶意或者错误的 INSERT 、 DELETE 和 UPDATE 操作，并强制执行 CHECK 约束定义的限制更为复杂的其他限制；
  • 评估数据修改前后表的状态，并根据该差异采取措施。

DDL触发器

DDL 触发器是当服务器或者数据库中发生数据定义语言（主要是以 CREATE ， DROP ， ALTER 开头的语句）事件时，被激活使用。使用 DDL 触发器，可以防止对数据架构进行的某些更改，还可以防止对记录数据中的更改或事件操作。

登录触发器

登录触发器会为响应 LOGIN 事件而激发存储过程，与 SQL Server 实例建立用户会话时，将引发此事件。

登录触发器将在登录的身份验证阶段完成之后，且用户会话实际建立之前激发。因此，来自触发器内部，且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

触发器的工作原理

触发器触发时：

  • 系统自动在内存中创建 deleted 表或 inserted 表；
  • 只读，不允许修改，触发器执行完成后，自动删除。

inserted表

  • 临时保存插入或更新后的记录行；
  • 可以从 inserted 表中，检查插入的数据是否满足业务需求；
  • 如果不满足，则向用户报告错误消息，并回滚插入操作。

deleted表

  • 临时保存删除或更新前的记录行；
  • 可以从 deleted 表中，检查被删除的数据是否满足业务需求；
  • 如果不满足，则向用户报告错误消息，并回滚插入操作。
    inserted 表和 deleted 表对照：

操作

inserted表

deleted表

insert

存放新增的记录

……

delete

……

存放被删除的记录

update

存放更新后的记录

存放更新前的记录

 

 

创建触发器

创建触发器语法示例如下：

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
AS
BEGIN
SQL语句
END

我们以 INSERT 触发器的创建为例，讲解触发器的创建和使用。首先创建测试数据表：

--创建学生表
create table student(
stu_id int identity(1,1) primary key,
stu_name varchar(10),
stu_gender char(2),
stu_age int
)

为 student 表创建 INSERT 触发器：

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
declare @stuNumber int;
select @stuNumber = count(*)from student;
if not exists (select * from student_sum)--判断表中是否有记录
insert into student_sum values(0);
update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

测试触发器：

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount
--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
select stuCount 学生总人数 from student_sum;

执行上面的语句后，结果如下图所示：

 

SQL Server DmL创建触发器格式 for_sql

 

另外，因为定义学生总数表 student_sum ，是向 student 表中插入数据后，才计算的学生总数。所以，学生总数表应该禁止用户，向其中插入数据。

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end

结果如下：

 

 

SQL Server DmL创建触发器格式 for_数据库_02

 

编程要求

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。 你只需：

  • 补全右侧代码片段中 create trigger_insert_score 下的 Begin-End 区域间的代码，向 score 表建立一个插入触发器。保证向 score 表中插入的学生信息的学号，必须在 student 表中存在；
  • 补全右侧代码片段 create trigger_delete_student 下的 Begin-End 区域间的代码，向 student 表插入删除触发器，实现 student 表和 score 表的级联删除；
  • 补全右侧代码片段 create trigger_protect_grade 下的 Begin-End 区域间的代码，向 score 表建立触发器，使 grade 列不能手工修改。

表 student 如下所示，其中 birthday 是 date 类型，其余字段均为 varchar 类型。

sno

sname

sex

birthday

discipline

school

1001

Tom

male

2019-06-01

computer

information

1002

Bob

male

2019-06-01

software

information

1003

Alice

female

2019-05-02

computer

information

表 score 如下所示，字段 grade 为 float 类型，其余均为 varchar 类型。

sno

cno

grade

1001

101

89

1001

102

78

1002

101

88

1002

102

80

测试说明

本关涉及到的测试文件是 step1.sh ，平台将运行用户补全的 step1.sql 文件，得到数据，然后执行如下操作：

  • 将得到的数据与答案比较，判断程序是否正确；
  • 如果操作正确，你将得到如下的结果：

 

 

SQL Server DmL创建触发器格式 for_数据库_03

开始你的任务吧，祝你成功！

实验代码

USE studentdb
go

SET NOCOUNT ON 
go
--********** create trigger_insert_score  **********--
--********** Begin **********--
create trigger trigger_insert_score
on score 
after insert 
as 
begin
if not exists 
(select* 
from student 
where sno in(
    select sno 
    from inserted))
begin
rollback transaction
begin transaction
end
end
--********** End **********--
go
delete from score
go
insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score
go


--********** create trigger_delete_student  **********--
--********** Begin **********--
create trigger trigger_delete_student
on student 
for delete
as
begin
delete from score 
where sno in(
    select sno 
    from deleted) 
end
--********** End **********--
go
delete from student 
where sno='1001'
go
select * 
from student
go

--********** create trigger_protect_grade **********--
--********** Begin **********--
create trigger trigger_protect_grade
on score 
for update
as
begin
rollback transaction
begin transaction
end
--********** End **********--
go
delete from score where sno='1001'
go
select * from score
go
select * from student
go