数据库管理实务（四） 触发器的实例分析

 触发器的实例分析

1.触发器是一种特殊存储过程。一般存储过程通过调用执行，而触发器是通过事件触发而被执行。触发器是功能强大的工具，可以实施对服务器、数据库和表等对象的事件监控，实现更复杂的数据完整性和安全性的管理。

2.SQL Server 2008中触发器分为3类：登录触发器、DDL触发器和DML触发器。

① 登录触发器。当用户登录，与实例建立会话时触发执行

• 由登录事件而激发的触发器，用户与SQL Server实例建立会话时将引发此事件。登录触发器在登录身份验证完成后且用户会话实际建立之前触发，因此在触发器内部通常将到达用户的所有消息（例如错误消息和来自Print语句的消息）发送到SQL Server错误日志。如果身份验证失败，将不激活登录触发器。
• 可以使用登录触发器审核和控制服务器会话，例如通过跟踪登录活动、限制SQL Server的登录名或限制特定登录名的会话数。

 

② DDL触发器。服务器或数据库发生DDL（数据定义语言）事件时触发执行。

• DDL触发器为了响应各种DDL事件而编制，主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。执行 DDL 式操作的存储过程也可以激发 DDL 触发器。DDL触发器只有在完成相应的DDL语句后才会被触发，因此无法作为Instead Of触发器使用。
• 可用于管理任务，如审核和控制数据库操作：要防止对数据库架构进行更改；希望数据库中发生某种情况以响应数据库架构中的更改；要记录数据库架构中的更改或事件等。

 

③ DML触发器。当数据库中发生DML（数据插入、删除、修改）操作事件时触发执行。DML操作针对的对象是表或视图。

• DML触发器针对INSERT、UPDATE和DELETE操作。分为AFTER触发器和INSTEAD OF触发器两种。
• AFTER触发器（又称后触发器）只能在表上定义，可对INSERT、UPDATE、DELETE操作定义触发器。当操作发生后，触发器被触发，可以对操作改变的数据进行检查，如果发现错误，可回滚变动的数据。可以针对表的同一操作定义多个触发器。
• INSTEAD OF触发器（又称替代触发器）在操作之前触发，不去执行操作，而是执行触发器定义的操作以取代之。INSTEAD OF触发器既可在表上定义也可以在视图上定义，但对同一操作只能定义一个INSTEAD OF触发器。

 

3.触发器-主要特点和作用

1. 触发器通过事件自动执行，因此可用于对用户登录、服务器或数据库上对象的创建、修改或删除，以及对表或视图的操作等进行监控。并可实现一些审计功能。
2. 触发器可以实现比CHECK约束、外键约束等方法更复杂的数据库完整性约束。
3. 可以增强数据库的安全性。
4. 回滚违反数据完整性的操作，防止恶意或错误的INSERT、UPDATE、DELETE操作。
5. 查找在数据修改前后，表状态之间的差别，并根据差别分别采取相应的措施。
6. 在一张表同一类型的操作上设置多个触发器，从而可以针对同样的DML语句执行不同的多种操作。

 

• 设计触发器

• 用户需求分析

假设某个公司的数据库中有两个表:accountData表和 wbf_UserTestAccountData 表。accountData 表记录了公司重要的资金信息，且只能由公司指定的财务人员使用，财务人员可以根据业务需要修改表中的数据。为了加强公司的财务管理，wbf_UserTestAccountData 表监督财务人员对资金的各种业务操作，确保资金运转安全高效。其中 accountData、wbf_UserTestAccountData 表的模式分别为:

accountData ( accounted，accountType，accountAmount)

wbf_UserTestAccountData ( wbf_UserTest_log_id，wbf_UserTest_log_loginname，wbf_UserTest_log_username，wbf_UserTest_log_actionType，wbf_UserTest_log_amount，wbf_UserTest_log_actionTime)

--测试表accountData

CREATE TABLE accountData

(

accounted INT, 

accountType varchar(20),

accountAmount INT,

)

--测试表wbf_UserTestAccountData

CREATE TABLE wbf_UserTestAccountData

(

wbf_UserTest_log_id INT, 

wbf_UserTest_log_loginname varchar(20),

wbf_UserTest_log_username varchar(20),

wbf_UserTest_log_actionType varchar(20),

wbf_UserTest_log_amount INT,

wbf_UserTest_log_actionTime DATE,

)

 

 

• 确定触发器的结构

触发器由时间条件、触发事件和动作组成。确定触发器的逻辑结构，就是确定触发器的时间条件、触发事件和动作以及触发器的选项由于这是一个审计触发器，那么只有在财务人员执行操作之后，才对这些操作进行记录。因此，可以确定该触发器的时间条件为 AFTER。确定了时间条件之后，接着开始研究触发器的事件类型，审计的对象是财务人员对帐户的所有操作，这些操作包括插入数据、删除数据和更新数据，可以确定该触发器的触发事件是 INSERT、DELETE和 UPDATE。所 以，可以 为 该 表 创 建 INSERT、DELETE、UPDATE 类型的 3 个触发器。触发器的条件和事件确定之后，就需要确定触发器本身的动作。这些将要确定的动作就是编SQL 语句来执行审计的记录工作。虽然在wbf_UserTestAccountData 表中包含6个列，但是只有 wbf_UserTest_log_actionType 列和 wbf_UserTest_log_amount 列需要填写财务人员执行操作类型值和金额，而其他 4 个列的值都可以由系统自动插入。

 

 

• 以DML触发器为例编写触发器

 

--创建INSERT触发器脚本命令

CREATE TRIGGER t_accountData_insert

ON accountData

WITH ENCRYPTION

FOR INSERT

AS

DECLARE @insertActionAmount MONEY

SELECT @i