sqlserver 触发器教程

触发器是SQL Server中的一种特殊类型的存储过程，它会在某个特定的数据库操作（如插入、更新或删除）发生时自动执行，触发器可以用于实现数据的完整性约束、审计和业务逻辑等，本文将对SqlServer触发器进行详细的介绍。

触发器的基本概念

1、触发器的定义：触发器是一种特殊的存储过程，它会在某个特定的数据库操作发生时自动执行，触发器可以用于实现数据的完整性约束、审计和业务逻辑等。

2、触发器的分类：根据触发时机的不同，触发器可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）。

3、触发器的语法：创建触发器的语法如下：

CREATE TRIGGER [触发器名称]
ON [表名]
[WITH ENCRYPTION]
AS
[IF EXISTS]
[;] BEGIN
    触发器逻辑
END;

触发器的创建与使用

1、创建触发器：创建触发器的语法如上所示，需要指定触发器名称、表名、触发时机、触发条件和触发器逻辑。

2、使用触发器：在对表进行插入、更新或删除操作时，触发器会自动执行，创建一个当向表employees插入数据时自动计算员工年龄的触发器：

CREATE TRIGGER tr_calculate_age
ON employees
AFTER INSERT
AS
BEGIN
    UPDATE employees
    SET age = YEAR(GETDATE()) YEAR(birthdate)
    FROM inserted i
    WHERE employees.id = i.id;
END;

触发器的优缺点

1、优点：

实现数据的完整性约束：通过在插入、更新或删除操作前后执行自定义的逻辑，可以实现数据的完整性约束。

审计功能：可以通过记录触发器的执行日志，实现对数据库操作的审计。

业务逻辑：可以将一些复杂的业务逻辑封装到触发器中，提高代码的可读性和可维护性。

2、缺点：

性能开销：触发器的执行会增加数据库的操作次数，可能导致性能下降。

调试困难：由于触发器是在特定操作发生时自动执行的，因此调试起来比较困难。

滥用风险：如果不加限制地使用触发器，可能导致数据库结构混乱，难以维护。

触发器的管理与优化

1、查看触发器：可以使用以下命令查看数据库中的触发器信息：

SELECT * FROM sys.triggers; SQL Server 2005及之前版本
SELECT * FROM sys.tables t INNER JOIN sys.triggers tr ON t.object_id = tr.parent_id; SQL Server 2008及之后版本

2、修改触发器：可以使用ALTER TRIGGER语句修改触发器的定义，修改上文创建的tr_calculate_age触发器：

ALTER TRIGGER tr_calculate_age
ON employees
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE employees
    SET age = YEAR(GETDATE()) YEAR(birthdate)
    FROM inserted i; 简化了WHERE子句，因为只有一个表inserted
END;

3、删除触发器：可以使用DROP TRIGGER语句删除触发器，删除上文创建的tr_calculate_age触发器：

DROP TRIGGER tr_calculate_age;

相关问题与解答

1、Q: 什么是行级触发器和语句级触发器？它们有什么区别？

A: 行级触发器（FOR EACH ROW）会在每次受影响的行上执行一次；而语句级触发器（FOR EACH STATEMENT）只会在整个语句执行完毕后执行一次，行级触发器适用于需要在每行上执行不同操作的场景，而语句级触发器适用于整个语句的逻辑相同，只需要在语句执行完毕后执行一次的场景。

2、Q: 如何查看数据库中的触发器信息？

A: 可以使用SELECT * FROM sys.triggers命令查看SQL Server 2005及之前版本的数据库中的触发器信息；对于SQL Server 2008及之后版本，可以使用SELECT * FROM sys.tables t INNER JOIN sys.triggers tr ON t.object_id = tr.parent_id命令查看。

3、Q: 如果我想在插入、更新和删除操作后都执行某个触发器，应该如何设置？

A: 可以在创建触发器时指定多个AFTER选项，AFTER INSERT, UPDATE, DELETE，这样，无论哪种操作发生，都会执行该触发器，但需要注意的是，这种情况下，触发器的执行顺序是按照指定的顺序依次执行的。