数据库触发器trigger 的应用

1. 什么是触发器（trigger)?

在某种操作执行的同时触发另一种操作。它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
比如有一张学生信息表，还有一张学生计数表，只要学生信息表添加一条信息，学生计数表就加1。

2. 触发器的种类

2.1 DML 触发器

DML (Data Manipulation Language，数据操作语言) 触发器是当数据库服务器中发生数据操作语言事件时执行的触发器。DML触发器又可以分为三种：

• 插入触发器 (insert) ：在向表中插入数据时被触发。
• 更新触发器 (update) ：在修改表中数据时被触发。
• 删除触发器 (delete) ：从表中删除数据时被触发。

♣ INSTEAD OF 触发器
这种触发器在数据修改操作之前触发，可以用来替换实际的数据修改操作。
此类触发器既可定义在表上，也可定义在视图上。
例如，对于DELETE 操作，如果我们期望只修改数据状态来标示数据已被删除而不是将数据从表中删除，那么我们可以使用INSTEAD OF 触发器来实现。
♣ AFTER 触发器
这种触发器在实际操作完成后触发，可以用来执行后续操作。
此类触发器只能定义在表上，不能创建在视图上。
例如，如果我们期望在删除数据后在其他表记录删除操作的发生时间，那么我们可以使用AFTER 触发器来实现。
♣ CLR 触发器
它允许在.NET Framework 中编写的代码在SQL Server 中执行。
CLR 触发器可以与DML 触发器和DDL 触发器一起使用，以在数据库中自动执行操作。
CLR触发器具有以下特点：
可访问性：CLR触发器可以使用C#或VB.NET等.NET语言编写，并且可以在SQL Server中直接执行。
扩展性：CLR触发器可以执行复杂的功能，包括数据验证、数据转换、日志记录等。
安全性：CLR触发器需要具有适当的权限才能在SQL Server中执行。
调试和部署：CLR触发器可以使用Visual Studio进行开发和调试，并且可以像其他.NET程序一样进行部署和维护。
CLR触发器的创建和使用需要具备.NET编程和SQL Server管理技能。在创建CLR触发器时，需要定义触发器的类型（AFTER或INSTEAD OF）、触发事件（INSERT、UPDATE或DELETE）以及要执行的.NET代码。然后，可以将CLR触发器附加到特定的表或视图上，以便在满足触发条件时自动执行相应的操作。

2.1.1 修改DML 触发器

    ALTER  TRIGGER触发器名
      ON  数据表名或视图名
      AFTER INSERT或DELETE或UPDATE
    AS
    BEGIN
        --这里是要运行的SQL语句
    END
    GO

修改触发器的名称

    sp_rename '旧触发器名','新触发器名'

2.1.2 删除DML 触发器

    Drop Trigger 触发器名

2.1.3 禁用与启用DML 触发器

    Alter table 数据表名
      Disable/Enable trigger 触发器名/ALL

2.2 DDL 触发器

DDL (Data Definition Language，数据定义语言) 触发器是当服务器或数据库中发生数据定义语言事件时被激活使用的触发器。DDL触发器主要用于防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

• create
• alter
• drop

2.3 登录触发器

登录触发器是当用户登录到SQL Server 实例时触发的触发器。它可以用于执行特定于用户登录的操作，例如记录审计日志或执行安全策略。

3. 触发器的优点

① 强制数据完整性
触发器可以在数据修改前后进行操作，确保数据的完整性。例如，在插入、更新或删除记录之前，可以检查某些条件是否满足。
② 实现复杂的业务规则
触发器可以用于实现复杂的业务规则，并在数据变更时自动执行。这可以减少应用程序中的逻辑复杂性。
③ 自动执行
触发器是自动执行的，这意味着当满足特定条件时，无需手动干预即可执行操作。
④ 级联操作
触发器可以检测数据库内的操作，并自动级联影响整个数据库的各项内容。
⑤ 嵌套调用
触发器可以调用一个或多个存储过程，这使得代码更加模块化和可重用。

4. 触发器的缺点

① 可移植性差
触发器是数据库特定的，这意味着从一个数据库系统迁移到另一个数据库系统时，可能无法直接使用触发器。
② 占用服务器资源
触发器在服务器上运行，可能会对服务器性能产生影响，尤其是在高并发的系统中。
③ 执行速度
触发器的执行速度可能会受到触发器代码的复杂性和数据库服务器性能的影响。
④ 嵌套调用的问题
如果触发器中的嵌套调用出现问题，可能会导致数据不一致，后期维护也较为不便。

5. 应用示例

5.1 触发器的创建

假设有一张学生成绩表 StudentInfo ，这张表进行了更新，插入和删除操作时，将这些操作数据备份到 SudentInfo_Backup 表中

--建表
if object_id('StudentInfo','u') is not null drop table StudentInfo
go
create table StudentInfo(
    Student_ID  INT
    ,Student_Name  varchar(20)
    ,Course      varchar(20)
    ,Score       int
)
go
insert into StudentInfo
values
 (1,'Jane','语文',90)
,(1,'Jane','数学',85)
,(2,'Bob','体育',78)
,(2,'Bob','英语',89)
,(3,'Wendy','数学',99)
go
if object_id('StudentInfo_Backup','u') is not null drop table StudentInfo_Backup
go
create table StudentInfo_Backup(
    Student_ID  INT
    ,Student_Name  varchar(20)
    ,Course      varchar(20)
    ,Score       int
    ,Operation  varchar(20)
    ,BackupTime   datetime
)
go




alter trigger [dbo].[Tri_StudentInfo_backup]  --触发器名称所在数据库里必须是唯一的
on [dbo].[StudentInfo]
for insert,update,delete
as

declare @Time datetime = getdate()

insert into StudentInfo_Backup (Student_ID,Student_Name,Course ,Score ,Operation,BackupTime)
select Student_ID,Student_Name,Course ,Score
	  ,'Update',@Time
from Inserted a
where exists (
	select 1 from Deleted b
	where a.Student_ID= b.Student_ID and a.Course = b.Course
)

insert into StudentInfo_Backup(Student_ID,Student_Name,Course ,Score ,Operation,BackupTime)
select Student_ID,Student_Name,Course ,Score
	  ,'Insert',@Time from Inserted a
where not exists (
	select 1 from Deleted b
	where a.Student_ID= b.Student_ID and a.Course = b.Course
)

insert into StudentInfo_Backup (Student_ID,Student_Name,Course ,Score
	  ,Operation,BackupTime)
select Student_ID,Student_Name,Course ,Score
	  ,'Delete',@Time from Deleted a
where not exists (
	select 1 from Inserted b
	where a.Student_ID= b.Student_ID and a.Course = b.Course
)

5.2 删除触发器

DROP TRIGGER Tri_StudentInfo_backup

5.3 sp_helptext 查看触发器文本信息

   sp_helptext  'triCV_MissAlignTable_History'

5.4 sp_help 查看触发器的名称、类型、创建时间等基本信息

    sp_help  'triCV_MissAlignTable_History'

5.5 如何知道触发器修改了多少条记录

假设有一张销售明细表 Sales ，建表语句如下：

--建表
if object_id('Sales','u') is not null drop table Sales
go
create table Sales (
    CustomerID   INT
    ,OrderID     INT
    ,SalesValue  INT
)
go
insert into Sales
values
 (1,1,190)
,(1,2,320)
,(2,3,80)
,(3,4,789)
,(4,5,450)
,(5,6,99)
go

创建一个触发器，显示删除了多少条记录

    CREATE TRIGGER deleteSalesData_test
      ON  Sales
      AFTER DELETE
    AS
    BEGIN
        print '您此次删除了' + Cast(@@rowcount as varchar) + '条记录'
    END
    GO

当删除数据时

Delete FROM Sales where SalesValue < 100
GO