SqlServer触发器详解

触发器是一种特殊的SQL Server对象，它对数据库进行操作时会自动触发。触发器通常用于在数据库中某些操作发生时自动执行一些逻辑处理。在本文中，我们将详细介绍SqlServer触发器并提供相关示例。

SqlServer触发器基础

1. SqlServer触发器类型

SqlServer触发器分为两类：

• DML触发器：在表的数据进行增删改时触发。

• DDL触发器：在表、存储过程、函数等对象发生变化时触发。

• SqlServer触发器语法

在SqlServer中创建触发器的基础语法如下：

CREATE TRIGGER [trigger_name] 
ON [table_or_view_name] 
[FOR/AFTER/INSTEAD OF] [DML_operation] 
AS
BEGIN
   -- 触发器逻辑处理
END

1. 触发器执行顺序

当一个表的DML触发器和DDL触发器同时存在时，SqlServer会按照以下顺序执行：

• DDL before 触发器
• DML before 触发器
• DML execution
• DML after 触发器
• DDL after 触发器

SqlServer触发器示例

示例1：在插入数据时自动更新另一张表的数据

在这个示例中，我们将创建一个触发器，使其在插入Sales表数据时自动更新Users表中相关用户的销售总额。

1. 创建Users表

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName NVARCHAR(50),
    TotalSales DECIMAL(18,2) DEFAULT 0
)

1. 创建Sales表

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    UserID INT,
    SaleAmount DECIMAL(18,2)
)

1. 创建触发器

CREATE TRIGGER update_total_sales
ON Sales
AFTER INSERT
AS
BEGIN
   -- 更新对应用户的销售总额
   UPDATE Users
   SET TotalSales = TotalSales + i.SaleAmount
   FROM Users INNER JOIN inserted i
   ON Users.UserID = i.UserID
END

该触发器的作用是当一个Sales对象插入到Sales表中时，对应用户的销售总额会自动更新。

示例2：在更新数据时使用事务进行保护

在这个示例中，我们将创建一个触发器，使用事务来保护更新操作，确保相关的数据完整性。

1. 创建Students表

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(50),
    TotalScore INT DEFAULT 0,
    TotalStudyTime INT DEFAULT 0
)

1. 创建触发器

CREATE TRIGGER update_student_info
ON Students
AFTER UPDATE
AS
BEGIN 
   -- 回滚事务
   IF @@TRANCOUNT = 0
      ROLLBACK;

   -- 检查更新数据是否符合条件
   IF EXISTS (
      SELECT 1 FROM inserted i WHERE i.TotalScore < i.TotalStudyTime
   )
   BEGIN
      RAISERROR('更新失败，总成绩不能小于总学习时间。', 16, 1);
      ROLLBACK;
      RETURN;
   END

   -- 更新相关学生数据
   UPDATE Students
   SET TotalScore = i.TotalScore,
       TotalStudyTime = i.TotalStudyTime
   FROM Students INNER JOIN inserted i
   ON Students.StudentID = i.StudentID
END

该触发器的作用是当执行UPDATE语句时，触发器会检查更新数据是否符合条件。若不符合条件，则会回滚事务并报错，保证相关数据的完整性。