触发器的已知限制

在 SQL Server 中构建与优化触发器

Florin Angelescu

Instructor

触发器的优点

用于数据库完整性
在数据库中直接实施业务规则
控制数据库允许的语句
由单个事件触发的复杂业务逻辑
简便的数据库与用户操作审计

触发器的缺点

难以查看与发现
对客户端应用或调试时不可见
故障排查时逻辑难以跟踪
可能增加服务器开销并降低性能

查找服务器级触发器

SELECT * FROM sys.server_triggers;

| name                 | parent_class_desc | create_date | is_disabled |...|
|----------------------|-------------------|-------------|-------------|---|
| DenyNewDatabases     | SERVER            | 2019-01-22  | 0           |...|
| DenyNewLinkedServers | SERVER            | 2019-01-22  | 1           |...|
| DenyDatabaseDelete   | SERVER            | 2019-01-22  | 1           |...|

查找数据库和表级触发器

SELECT * FROM sys.triggers;

| name                   | parent_class_desc | create_date | is_disabled | is_instead_of_trigger |
|----------------------|-------------------|-------------|---------------|-----------------------|
| TrackRetiredProducts   | OBJECT_OR_COLUMN  | 2019-04-19  | 0           | 0                     |
| PreventOrdersUpdate    | OBJECT_OR_COLUMN  | 2019-04-22  | 0           | 1                     |
| TrackDatabaseTables    | DATABASE          | 2019-04-23  | 0           | 0                     |
| KeepCanceledOrders     | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 0                     |
| DiscountsPreventDelete | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventNewDiscounts    | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventTableDeletion   | DATABASE          | 2019-04-27  | 0           | 0                     |

查看触发器定义（方法 1）

从 SSMS 查看触发器定义

CREATE TRIGGER PreventOrdersUpdate
ON Orders
INSTEAD OF UPDATE
AS
RAISERROR ('Updates on "Orders" table
            are not permitted.
            Place a new order
            to add new products.', 16, 1);

查看触发器定义（方法 2）

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID ('PreventOrdersUpdate');

| definition                                                   |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

查看触发器定义（方法 3）

SELECT OBJECT_DEFINITION (OBJECT_ID ('PreventOrdersUpdate'));

| (No column name)                                             |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

查看触发器定义（方法 4）

EXECUTE sp_helptext @objname = 'PreventOrdersUpdate';

| Text                                                         |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

触发器最佳实践

提示：

良好记录的数据库设计
触发器逻辑保持简单
避免过度使用触发器

Vamos praticar!

在 SQL Server 中构建与优化触发器