SqlServer触发器的创建与使用

前言

上期我们介绍了SqlServer的视图和存储过程创建与使用，这期我们介绍一下触发器。

有需要回顾的可以电梯直达看一下：

SqlServer视图的创建与使用

SqlServer存储过程的创建与使用

---

 

触发器的定义

触发器（Trigger）是针对某个表或视图所编写的特殊存储过程，它不能被显式地调用，

而是当该表或视图中的数据发生添加INSERT、更新UPDATE或删除DELETE等事件时自动被执行。

主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。

触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，

它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。

触发器的主要作用主要有以下接个方面：

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

触发器的优点

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

触发器的缺点

1. 增加了系统的复杂性。
2. 嵌套触发器容易出现死锁现象。
3. 触发器并没有提升多少性能。
4. 可移植性差。
5. 占用服务器资源，给服务器造成压力。
6. 复杂的触发器维护困难。

---

触发器的分类

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

---

 

DML触发器

如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。

DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。

在激发任何有效的事件时，将会激发这些触发器，而无论是否会影响任何表行。

当数据库服务器中发生数据操作语言事件时执行这些操作。

SqlServer中的DML触发器有三种：

1. insert触发器:向表中插入数据时被触发；
2. delete触发器：从表中删除数据时被触发；
3. update触发器：修改表中数据时被触发。

 

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

1. 通过数据库中的相关表实现级联更改

2. 防止恶意或者错误的insert、update和delete操作，并强制执行check约束定义的限制更为复杂的其他限制。

3. 评估数据修改前后表的状态，并根据该差异才去措施。

---

DDL触发器

DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，

使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

 

---

登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。

与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。

因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。

如果身份验证失败，将不激发登录触发器。

---

 

触发器的种类

 

after触发器（之后触发）

在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器。

如果违反了约束，则永远不会执行 AFTER 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。

对于在 MERGE 语句中指定的每个 INSERT、UPDATE 或 DELETE 操作，将为每个 DML 操作触发相应的触发器。

 

instead of 触发器

INSTEAD OF触发器替代下列触发语句的标准操作。

因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。

INSTEAD OF触发器的主要优点是可以使不能更新的视图支持更新。

INSTEAD OF触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

 

下表对 AFTER 触发器和 INSTEAD OF 触发器的功能进行了比较。

 

Transact-SQL DDL 触发器

用于执行一个或多个 Transact-SQL 语句以响应服务器范围或数据库范围事件的一种特殊类型的 Transact-SQL 存储过程。

例如，如果执行某个语句（如 ALTER SERVER CONFIGURATION）或者使用 DROP TABLE 删除某个表，则激发 DDL 触发器。

 

CLR DDL 触发器

CLR 触发器将执行在托管代码（在 .NET Framework 中创建并在 Transact-SQL 中上载的程序集的成员）中编写的方法，而不用执行 SQL Server存储过程。

 

---

 

使用T-SQL语句来增删查改触发器 

创建触发器

--创建一个触发器
--检测到表数据有update时，更新CreateTime时间为当前时间
create trigger trigger_a_Students  --触发器名称
on a_Students for update
as
begin
update a_Students SET CreateTime=GETDATE()  --触发器需要执行的sql
end

--测试
--执行一个修改语句
update a_Students
set Name='孙悟空1'
where Number=100001

--查看数据
select * from a_Students

 

删除触发器

--删除触发器
drop trigger trigger_a_Students   --触发器名称

 

查询触发器

--查看所有触发器
select * from sysobjects where xtype='TR' 

-- 查看单个触发器 
exec sp_helptext 'trigger_a_Students'   --触发器名称

 

修改触发器

--修改一个触发器
--检测到表数据有update，insert时，更新CreateTime时间为当前时间
alter trigger trigger_a_Students  --触发器名称
on a_Students for update,insert
as
begin
update a_Students SET CreateTime=GETDATE()  --触发器需要执行的sql
end

--测试
--执行一个插入语句
INSERT INTO a_Students(Number,Name,ClassId) 
VALUES('100016','李靖',3)

--查看数据
select * from a_Students

 

好了，我们就介绍到这里吧，

拜拜，我们下次不见不散。

 

欢迎关注订阅微信公众号【熊泽有话说】，更多好玩易学知识等你来取
作者：熊泽-学习中的苦与乐
公众号：熊泽有话说
出处： https://www.cnblogs.com/xiongze520/p/14606862.html
创作不易，任何人或团体、机构全部转载或者部分转载、摘录，请在文章明显位置注明作者和原文链接。