目录

• 引言
  • 魔术表
• 存储过程和触发器之间的区别
• DML 触发器
  • After Triggers
    • After 触发器的语法
    • After 插入触发器示例
    • After 删除触发器示例
    • After 更新触发器示例
  • Instead of Triggers
• DDL 触发器
  • Create Table 的 DDL 触发器
  • Alter Table 的 DDL 触发器
  • Drop Table 的 DDL 触发器
• 嵌套触发器
• 递归触发器
• 如何在数据库中查找触发器
• 如何禁用触发器
• 如何启用触发器
• 如何删除触发器
• 真实生活示例
• SQL 触发器的优点
• 触发器的缺点
• 摘要

引言

触发器可以定义为数据库对象，当用户尝试在指定的表上执行数据修改命令（INSERT、DELETE 和 UPDATE）时，它们会自动执行某些操作。触发器绑定到特定的表。根据 **MSDN**，触发器可以定义为特殊类型的存储过程。本文“SQL Triggers – An Introduction”将为您提供有关 SQL Triggers 的详细知识，这在您的工作中将非常有用。在描述触发器类型之前，我们应该先了解触发器中引用的魔术表，这些魔术表用于重用。

魔术表

SQL Server 中有两个表 Inserted 和 deleted，它们被称为魔术表。它们不是物理表，而是 SQL Server 内部表，通常与触发器一起使用以检索插入、删除或更新的行。这些表包含有关插入的行、删除的行和更新的行的信息。这些信息可总结如下

操作	Inserted	Deleted
Insert	表包含所有插入的行	表不包含任何行
删除	表不包含任何行	表包含所有删除的行
更新	表包含更新后的行	表包含更新前的所有行

存储过程和触发器之间的区别

1. 我们可以使用 exec 命令随时执行存储过程，但触发器只能在定义的表上触发事件（insert、delete 和 update）时执行。
2. 我们可以在另一个存储过程内部调用存储过程，但我们不能在触发器内直接调用另一个触发器。我们只能实现触发器的嵌套，其中触发器内定义的操作（insert、delete 和 update）可以启动在同一表或不同表上定义的另一个触发器的执行。
3. 存储过程可以通过作业安排在预定义的时间执行，但我们无法安排触发器。
4. 存储过程可以接受输入参数，但我们不能将参数作为输入传递给触发器。
5. 存储过程可以返回值，但触发器不能返回值。
6. 我们可以在存储过程中使用 Print 命令进行调试，但不能在触发器中使用 print 命令。
7. 我们可以在存储过程中使用事务语句，如 begin transaction、commit transaction 和 rollback，但不能在触发器中使用事务语句。
8. 我们可以从前端（.asp 文件、.aspx 文件、.ascx 文件等）调用存储过程，但不能从这些文件中调用触发器。

DML 触发器

触发器类型

在 SQL Server 中，有两种类型的触发器，如下所示：

1.    After Triggers
2.    Instead of Triggers

在本文中，我们将使用名为 customer、customerTransaction 和 Custmail 的三个表，其结构如下：

Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)
Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)
Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))

After Triggers

“After Triggers”在相应表上的数据修改操作（Insert、Delete、Update）执行后执行。一个表可以定义多个触发器。

After 触发器的语法

Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin
 //SQL Statements
End

After 插入触发器示例

假设我们的需求是，每当添加一个新客户时，其对应值必须自动插入到 Custmail 表中，以便可以向客户和银行的授权人员发送电子邮件。为了解决这个问题，我们可以在 customer 表上创建一个 After Insert 触发器，其语法如下：

Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar(100)
Declare @amount as int
Declare @custid as int
 Select @Custnumber=Custnumber, @amount=Amount From inserted
 Select @custid=customerid From customer Where Custnumber =@Custnumber 
 Insert Into Custmail (custid,Amt,Mailreason) 
 Values (@custid,@amount,'New Customer')
End

每当向银行添加新客户并将其对应条目插入 Custmail 表时，都会触发此触发器。邮件功能将使用 custmail 表中的条目向客户发送邮件。

After 删除触发器示例

假设还有一个需求，每当从系统中删除一个客户时，都会向客户发送一封包含删除通知的电子邮件。为了发送邮件，每当从主表 customer 中删除一个客户时，我们需要将该客户的条目插入 custmail 表。为了实现这一点，我们将使用删除的 After 触发器。在下面的示例中，我们将使用魔术表 Deleted。

Create trigger trig_custdelete on customer
For Delete
As begin
 Declare @Custnumber as nvarchar(100)
 Declare @custid as int
Select @Custnumber=Custnumber from deleted
Select @custid=customerid from customer where Custnumber =@Custnumber
Delete from customerTransaction where custid=@custid
Insert into Custmail
Values(@custid,0,'Customer delete')
end

After 更新触发器示例

假设我们还有一个需求，每当客户的账户充值或姓名（包括名和姓）更新时，都应向客户发送一封包含此信息的邮件。在这种情况下，我们可以使用更新的 After 触发器。在此示例中，我们将使用 Magic table Inserted。

create trigger trig_Custupdate
on customer
for update
as
begin
  declare @Custnumber as nvarchar(100)
  declare @amount as int
  Declare @custid as int
  if update(amount)
    begin
        select @Custnumber=Custnumber, @amount=Amount from inserted
        select @custid=customerid from customer where Custnumber =@Custnumber
        insert into Custmail 
        values(@custid,@amount,'Customer Amount Update')
    end
  if update(custFname)or update(CustEnamn)
    begin
   insert into Custmail 
   values(@custid,0,'Customer Name Update')
    end
end

在上面的示例中，我们使用了 amount、custfname 和 custEname 列的 Update 函数，该函数会在修改这些列时触发更新触发器。

Instead of Triggers

当我们想要执行触发器而不是导致触发器执行的操作时，将使用 Instead of 触发器。可以为 Insert、Delete 和 Update 定义 Instead of 触发器。例如，假设我们的条件是，在一个事务中，用户一次最多只能提取 15000 美元。我们可以使用 Instead of 触发器来实现此约束。如果用户尝试一次从其账户中提取超过 15000 美元，则会引发错误，消息为“一次不能提取超过 15000 美元”。在此示例中，我们使用魔术表 Inserted。

Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar(100)
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar(10)
select @custid=custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode='c'
begin
update customer set amount=amount+@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
if @mode='d'
begin
if @amount<=15000
begin
update customer set amount=amount-@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
else
begin
Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
rollback;
end
end
end

DDL 触发器

DDL 触发器的行为与 DML 触发器类似，除了它们是在响应 DDL 类型事件（如 Alter 命令、Drop 命令和 Create 命令）时触发的。换句话说，它将响应尝试更改数据库模式的事件而触发。因此，这些触发器不是为特定表创建的，而是适用于数据库中的所有表。此外，DDL 触发器只能在使其触发的命令执行后触发。它们可用于以下目的：

1. 防止对数据库模式进行任何更改
2. 如果我们想存储所有更改数据库模式的事件的记录。

例如，假设我们想创建一个表 command_log，它将存储所有用于创建表的（Create table）用户命令以及用于更改表的命令。我们也不希望任何表被删除。因此，如果触发了任何 drop table 命令，DDL 触发器将回滚该命令，并显示消息“您不能删除表”。

command_log 表的脚本将在下面给出

CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))

Create Table 的 DDL 触发器

为了将 create table 命令存储在 command_log 表中，我们首先需要创建一个触发器，该触发器将在响应 Create table 命令的执行时触发。

CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT 'Table has been successfully created.'
insert into command_log ()
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')

End

每当触发表创建命令时，都会触发此触发器，并将命令插入 command_log 表，并打印消息“表已成功创建”。

注意： Eventdata() 是一个函数，它返回有关服务器或数据库事件的信息。它返回 XML 类型的值。 详细了解 Eventdata()

Alter Table 的 DDL 触发器

假设我们还想将 alter table 命令存储在 command_log 表中，我们需要为 Alter_table 命令创建一个触发器。

Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print 'Table has been altered successfully'
insert into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')

end

每当在数据库上执行任何 alter table 命令时，都会触发此触发器，并打印消息“表已成功更改。”

Drop Table 的 DDL 触发器

为了阻止用户删除数据库中的任何表，我们需要为 drop table 命令创建一个触发器。

Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT 'Table cannot be dropped.'
INSERT into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
Rollback;
end

此触发器不允许删除任何表，并打印消息“表不能被删除。”

嵌套触发器

嵌套触发器：- 在 SQL Server 中，当一个触发器的动作启动另一个可能位于同一表或不同表上的触发器时，这些触发器就被称为嵌套触发器。
例如，假设有一个在表 tbl1 上定义的触发器 t1，还有一个在表 tbl2 上定义的触发器 t2。如果触发器 t1 的动作启动了触发器 t2，那么这两个触发器就被称为嵌套触发器。在 SQL Server 中，触发器可以嵌套到 32 个级别。如果嵌套触发器的操作导致无限循环，则在 32 级之后，触发器将终止。
由于触发器是在事务中执行的，因此嵌套触发器内的任何级别的失败都会取消整个事务，并导致完全回滚。

我们还可以通过以下 SQL 命令停止执行嵌套触发器

sp_CONFIGURE 'nested_triggers',0<br style="padding: 0px; margin: 0px;" />GO
RECONFIGURE
GO

递归触发器

在 SQL Server 中，我们可以使用递归触发器，其中一个触发器的操作可以再次启动它自身。在 SQL Server 中，我们有两种类型的递归。

1.   直接递归
2.   间接递归

在直接递归中，一个触发器的动作会再次启动触发器本身，导致触发器递归调用自身。
在间接递归中，一个触发器的动作会启动另一个触发器，而那个触发器的执行又会再次调用原始触发器，并递归地发生这种情况。这两个触发器可以位于同一表上，也可以创建在不同的表上。

请注意：只有在设置了递归触发器选项时，才可能发生递归触发器。

可以使用以下 SQL 命令设置递归触发器选项

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

如何在数据库中查找触发器

1. 查找数据库中的所有触发器
   假设我们想获取所有触发器及其各自表名的列表，那么我们可以使用以下 SQL 语句。

   select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'

   

2. 查找特定表上的所有触发器

   例如，如果我们想找出在 Customer 表上创建的所有触发器，那么我们可以使用以下 SQL 语句

   sp_helptrigger Tablename
   example:-
   sp_helptrigger 'Customer'

   

3. 查找触发器的定义

   假设如果我们想找出触发器的定义，我们可以使用以下 SQL 语句

   sp_helptext triggername
   For example:-
   sp_helptext 'trig_custadd'

   结果

   

如何禁用触发器

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

禁用表上的 DML 触发器

DISABLE TRIGGER 'trig_custadd' ON Customer;

禁用 DDL 触发器

 DISABLE TRIGGER 'DDL_Createtable' ON DATABASE;

禁用使用相同范围定义的所有触发器

DISABLE Trigger ALL ON ALL SERVER;

如何启用触发器

启用表上的 DML 触发器

ENABLE Trigger 'trig_custadd'  ON Customer;

启用 DDL 触发器

ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;

启用使用相同范围定义的所有触发器

ENABLE Trigger ALL ON ALL SERVER;

如何删除触发器

删除 DML 触发器

DROP TRIGGER trig_custadd ;

删除 DDL 触发器

DROP TRIGGER DDL_Createtable ON DATABASE;

真实生活示例

几周前，我的一个朋友接到了一个需要在一个非常旧的代码上完成的任务。任务包括在以下情况下向用户发送电子邮件：

1. 用户被添加到系统中。
2. 每当用户的信息被更新、删除或添加时。
3. 用户被删除。

此任务中的挑战包括

1. 代码非常旧且结构混乱。因此，它在各种 .aspx 页面上编写了许多内联查询。
2. 插入、删除和更新的查询也编写在许多存储过程中。

因此，代码没有通用的库函数或标准存储过程贯穿整个应用程序，可用于插入、更新和删除用户，这不是一个好习惯。但有时旧代码就是这样。所需的查询编写在许多 .aspx 页面和存储过程中。

可能的解决方案

要完成此任务，我们需要在 tblmail 表中插入一条记录，并带有指示插入、删除和更新的正确标志。一个用 .net 应用程序构建的计划应用程序将读取 tblmail 表中的行并发送邮件。

插入记录的两种方法

1. 查找 .aspx 文件和存储过程中的所有查询，并在这些查询之后，为 tblmail 表添加插入查询。
2. 而不是在所有 .axps 文件和存储过程中查找这些查询，在用户主表上创建 after（insert、update 和 delete）触发器将在 insert、update 和 delete 语句执行后将日期插入 tblmail 表。

我们之所以采用第二种方法，是因为以下 4 个原因

1. 搜索如此多的 .aspx 文件和存储过程以查找所需查询非常困难。
2. 存在一个风险，即新开发人员可能不知道发送邮件的这个要求，并且忘记为向 tblmail 表插入值添加代码。
3. 如果我们需要更改任何需求，则必须在所有这些文件和存储过程中进行更改。
4. 通过第二种方法，我们只需要在表上创建触发器，并且开发人员，并且它还将最大程度地减少上述三点中提到的风险。

SQL 触发器的优点

1. 它有助于维护数据库表中的完整性约束，尤其是在未定义主键和外键约束时。
2. 它有时也有助于使 SQL 代码保持简短和简单，正如我在真实生活示例中所示。
3. 它有助于通过将更改的值插入审计表来跟踪表中发生的所有更改（更新、删除和插入）。
4. 有时，如果代码管理不善，它有助于维护在表上定义了触发器的数据库约束。例如，假设有一个在线学习系统，用户可以在多个课程中注册。

假设组织希望定义一个约束，即用户在通过所有注册课程之前不能被删除，或者用户必须首先从所有未完成的或失败的课程中取消注册。

由于代码管理不善，并且删除用户的代码定义在许多 .net 页面和多个存储过程中（这不是一件好事，但确实会发生），因此必须将强制执行此约束的代码写入所有这些 .net 文件和存储过程中，这需要花费大量时间，并且如果新开发人员不知道此约束并且忘记包含约束强制代码，则会损坏数据库。在这种情况下，我们可以在表上定义一个 instead of 触发器，该触发器每次在删除用户时进行检查，如果上述约束条件不满足，则显示错误消息而不是删除用户。

触发器的缺点

1. 难以维护，因为新开发人员可能不知道数据库中定义的触发器，并怀疑数据为何会自动插入、删除或更新。
2. 它们难以调试，因为与存储过程、视图、函数等相比，它们更难查看。
3. 过度使用触发器会降低应用程序的性能，因为如果在许多表上定义了触发器，那么每次在表（根据触发器的定义）中插入、删除或更新数据时，它们都会自动执行，这使得处理过程非常缓慢。
4. 如果触发器中编写了复杂的代码，那么它将降低应用程序的性能。
5. 在 DML（插入、删除和更新）操作频率很高（如批量插入）的表上创建触发器的成本可能更高。

摘要

触发器的好坏取决于其用途和适当的文档。当它用于在没有主键和外键的情况下维护数据库表中的完整性约束时，它可能非常有用，或者对于跟踪所有更改的审计目的非常有用。但是，如果使用过多，它会降低性能。此外，为了维护它并简化调试，必须对触发器进行适当的文档记录，其中记录触发器名称、创建它的表名、其定义和目的。

这篇 SQL Triggers – An Introduction 文章首次出现在 Technology with Vivek Johari。