SQL Server 触发器实例详解

SQL Server 触发器实例详解

什么是SQL Server触发器？

SQL Server 触发器是一段特殊的代码，可以自动地在特定情况下执行。当执行到 SQL Server 数据库上的特定动作时，触发器将会被激活，并执行预定义好的代码。触发器通常被用来在数据库发生变化时执行额外的自定义代码。

SQL Server 触发器的分类

SQL Server 触发器总共分为两种类型：

• 行级触发器：是在数据的每一行被插入、更新或删除时，都会自动运行一次的触发器。
• 语句级触发器：是在 SQL 数据库上的某个特定语句运行时立即自动运行的触发器。

SQL Server 触发器的语法

CREATE TRIGGER trigger_name 
{ AFTER | INSTEAD OF } {update,delete,insert} 
ON {table_name} 
[REFERENCING [{OLD | NEW} AS alias_name}]
[FOR EACH {ROW | STATEMENT}]
[WHEN (logical_expression)]
{sql_statement}

具体来说，该语法有以下几个重要的部分。

• CREATE TRIGGER：触发器的创建命令。
• trigger_name：触发器的名字。
• { AFTER | INSTEAD OF }：指明触发器是在执行操作之后还是代替执行操作之前运行。
• {update,delete,insert}：指明在何种操作时运行触发器。
• ON {table_name}：指明在哪张表上运行触发器。
• [REFERENCING [{OLD | NEW} AS alias_name}]：指明“OLD”或“NEW”关键字。这可以帮助我们引用旧值或新值。
• [FOR EACH {ROW | STATEMENT}]：指明触发器是对每一行记录执行还是对整个语句执行。
• [WHEN (logical_expression)]：定义逻辑测试。如果测试失败，则该触发器不会被触发。
• {sql_statement}：需要在触发器中执行的 SQL 语句。

SQL Server 触发器创建示例

示例1：创建一个简单的SQL Server触发器

在 AdventureWorks 数据库上创建一个员工信息表，并在该表上创建一个触发器，以确保每个新员工的雇佣日期按年月日顺序执行。

USE AdventureWorks
GO

CREATE TABLE Employee
(
 EmployeeID INT PRIMARY KEY IDENTITY(1,1),
 LastName VARCHAR (50) NOT NULL,
 FirstName VARCHAR (50),
 HireDate DATE
)
GO

CREATE TRIGGER tr_insert_employee
ON Employee
FOR INSERT
AS
BEGIN
 IF EXISTS (
 SELECT 1
 FROM inserted i
 WHERE i.HireDate < CAST(GETDATE() AS DATE)
 )
 BEGIN
 PRINT 'Cannot insert employee with hire date before today!'
 ROLLBACK TRANSACTION
 RETURN
 END

 DECLARE @insertedID INT
 SELECT @insertedID = [EmployeeID] FROM INSERTED

 UPDATE Employee SET [HireDate] = CAST(CONVERT(VARCHAR(10), [HireDate], 111) AS DATE) WHERE EmployeeID = @insertedID
END

示例2：创建一个基于多个表的SQL Server触发器

在 AdventureWorks 数据库上创建两个员工信息表：Employee和EmployeeBackup，并在这两个表上创建触发器，以确保当 Employee 中的数据更新时，EmployeeBackup 中的对应数据也会得到更新。

USE AdventureWorks
GO

CREATE TABLE Employee
(
 EmployeeID INT PRIMARY KEY IDENTITY(1,1),
 LastName VARCHAR (50) NOT NULL,
 FirstName VARCHAR (50),
 HireDate DATE
)
GO

CREATE TABLE EmployeeBackup
(
 EmployeeID INT PRIMARY KEY,
 LastName VARCHAR (50) NOT NULL,
 FirstName VARCHAR (50),
 HireDate DATE
)
GO

CREATE TRIGGER tr_update_employee
ON Employee
AFTER UPDATE
AS
BEGIN
 UPDATE EmployeeBackup
 SET LastName = i.LastName,
     FirstName = i.FirstName,
     HireDate = i.HireDate
 FROM EmployeeBackup e
 INNER JOIN inserted i ON i.EmployeeID = e.EmployeeID
END