sql编程介绍触发器

什么是触发器？

触发器是一段自动化的程序，可以在表上的某些特定事件发生时执行。触发器可以监视表的插入、更新、删除操作等，并在符合特定条件的情况下，触发执行一定的操作。在 SQL Server 中，触发器是一种特殊类型的存储过程，设计的目的是在特定的数据操作发生时执行，然而它们与标准的存储过程有很大的不同之处。最大的不同之处是，在处理事务时，触发器是在数据修改之前或之后执行的，而存储过程则是在数据修改之后执行的。

触发器的类型

在 SQL Server 中，触发器可以分为三种类型：在插入前触发的触发器（Before Insert Trigger）、在修改前触发的触发器（Before Update Trigger）和在删除前触发的触发器（Before Delete Trigger）。

1.在插入前触发的触发器 (Before Insert Trigger)

该触发器是在插入数据之前执行的，可以捕获并处理插入数据之前的任何事件，比如检查插入的数据是否符合特定的条件，如果不符合，可以拒绝插入，并返回一条错误信息。

例如，当客户在订单表中添加新订单记录时，可以在添加之前验证订购的商品数量。如果订购的数量大于存储在另一个表中的最大数量，则可以通过触发器自动拒绝该记录的插入。

2.在修改前触发的触发器 (Before Update Trigger)

该触发器是在修改数据之前执行的，可以捕获并处理修改数据之前的任何事件，比如检查修改的数据是否符合特定的条件，如果不符合，可以拒绝修改，并返回一条错误信息。

例如，当客户在订单表中修改订单记录时，可以在提交修改之前验证修改过的订单是否符合条件。如果修改后的订单总金额超出了存储在另一个表中的客户信用额度，则可以通过触发器自动拒绝该记录的修改。

3.在删除前触发的触发器 (Before Delete Trigger)

该触发器是在删除数据之前执行的，可以捕获并处理删除数据之前的任何事件，比如检查删除的数据是否符合特定的条件，如果不符合，可以拒绝删除，并返回一条错误信息。

例如，当客户在订单表中删除订单记录时，可以在提交删除之前验证删除的订单是否符合条件。如果该订单已被其他表所引用，则可以通过触发器自动拒绝该记录的删除。

触发器的使用方法

创建触发器的语法如下：

CREATE TRIGGER 触发器名称

{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON 表名称

[ FOR EACH ROW ]

BEGIN

[触发器内的T-SQL语句]

END;

说明：

1. 触发器名称：为触发器指定一个名称，名称必须是在当前数据库中唯一的。

2. BEFORE/AFTER：指定触发器的执行时间，在操作之前或之后执行。

3. INSERT/UPDATE/DELETE：指定触发器监视的数据操作类型。

4. 表名称：指定触发器监视的表名。

5. FOR EACH ROW：指定触发器是对每个受影响的行执行还是对整个操作执行一次。在 SQL Server 中，默认情况下每个操作只会触发一次触发器。

6. 触发器内的 T-SQL 语句：指定要在触发器中执行的 T-SQL 语句。

下面是一个例子，演示了如何创建一个在插入之前触发的触发器，将插入的数据中的用户名转换成大写形式：

CREATE TRIGGER [dbo].[trigger_name]

ON [dbo].[table_name]

BEFORE INSERT

AS

BEGIN

UPDATE INSERTED SET [username]=UPPER([username]);

END;

在该触发器中，通过使用 INSERTED 表来访问正在插入的行的数据，使用 UPPER 函数将 username 字段转换成大写形式。

触发器实战案例

以下是一个示例说明了如何创建一个触发器以自动向经理发送电子邮件，提示他们当员工的工资被提高时发生了什么。

首先，需要创建一个存储过程，将员工信息和工资变化信息插入到一个名为 Employee_History 的表中：

CREATE PROCEDURE [dbo].[UpdateEmployeeSalary]

@EmployeeID INT,

@NewSalary FLOAT

AS

BEGIN

SET NOCOUNT ON

UPDATE Employee SET [Salary] = @NewSalary WHERE [EmployeeID] = @EmployeeID

INSERT INTO Employee_History ([EmployeeID], [OldSalary], [NewSalary], [ChangeDate]) VALUES (@EmployeeID, @OldSalary, @NewSalary, GETDATE())

END;

接下来，创建一个触发器，在员工的工资被修改之后发送一封邮件：

CREATE TRIGGER [dbo].[SalaryChangeTrigger]

ON [dbo].[Employee]

AFTER UPDATE

AS

IF UPDATE([Salary]) -- 检查工资是否被更改

BEGIN

DECLARE @ManagerEmail VARCHAR(100)

SET @ManagerEmail = (SELECT [EmailAddress] FROM [dbo].[Employee] WHERE [EmployeeID] = (SELECT [ManagerID] FROM [dbo].[Employee] WHERE [EmployeeID] = (SELECT employeeID FROM inserted)))

-- 获取经理的电子邮件地址

DECLARE @EmployeeName VARCHAR(100)

DECLARE @OldSalary FLOAT

DECLARE @NewSalary FLOAT

SELECT @EmployeeName = [LastName] + ', ' + [FirstName], @OldSalary = [Salary], @NewSalary = i.[Salary] FROM [dbo].[Employee], inserted i WHERE [Employee].[EmployeeID] = i.[EmployeeID]

DECLARE @Subject NVARCHAR(200)

SELECT @Subject = 'Salary increase for ' + @EmployeeName

DECLARE @Body NVARCHAR(200)

SELECT @Body = 'Employee ' + @EmployeeName + ' salary has been changed from ' + CAST(@OldSalary AS VARCHAR) + ' to ' + CAST(@NewSalary AS VARCHAR)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', -- 指定邮件配置文件（如果未指定，则使用默认配置文件）

@recipients = @ManagerEmail, -- 经理的电子邮件地址

@subject = @Subject, -- 邮件主题

@body = @Body -- 邮件主体

END

在该触发器中，首先检查工资是否被更改，如果是，就获取员工的经理的电子邮件地址，并构建邮件的主题和正文。最后，使用系统存储过程 sp_send_dbmail 发送邮件。

总结

触发器是 SQL Server 中一个很强大的工具，可以用来自动化一些常见的操作和任务。触发器的使用可以提高数据的完整性和准确性，同时也可以增强应用程序的安全性。在使用触发器时，需要注意触发器的类型和执行时间，同时也需要谨慎地设计触发器，保证不会影响数据库性能。

壹涵网络我们是一家专注于网站建设、企业营销、网站关键词排名、AI内容生成、新媒体营销和短视频营销等业务的公司。我们拥有一支优秀的团队，专门致力于为客户提供优质的服务。

我们致力于为客户提供一站式的互联网营销服务，帮助客户在激烈的市场竞争中获得更大的优势和发展机会！