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

时间：2024-03-18 20:47:38

 触发器的实例分析

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 个列的值都可以由系统自动插入。

 

 

 

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

CREATE TRIGGER t_accountData_insert

ON accountData

WITH ENCRYPTION

FOR INSERT

AS

DECLARE @insertActionAmount MONEY

SELECT @insertActionAmount = accountAmount

FROM inserted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

VALUES ('INSERT', @insertActionAmount)

RETURN

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

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

CREATE TRIGGER t_accountData_delete

ON accountData

WITH ENCRYPTION

FOR DELETE

AS

DECLARE @deleteActionAmount MONEY

SELECT @deleteActionAmount = accountAmount

FROM deleted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

VALUES ('DELETE',@deleteActionAmount)

RETURN

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

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

use wbf

go

CREATE TRIGGER t_accountData_update

ON accountData

WITH ENCRYPTION

FOR UPDATE

AS

DECLARE @oldValue MONEY

SELECT @oldValue = accountAmount FROM deleted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

VALUES ('update_old_value ',@oldValue)

DECLARE @newValue MONEY

SELECT @newValue = accountAmount FROM inserted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

VALUES ('update_new_value ',@newValue)

RETURN

 

 

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

 

触发器创建之后，在正式使用之前，应该对触发器进行测试。测试的目的是保证建立了正确的触发器，能够正常工作。首先测试插入数据的操作。下面是一组插入数据的例子，把这些数据插入到 accountData 表中。这些数据插入之后，会触发 t_accountData_insert

触发器的执行。

--一组插入数据的操作:

INSERT INTO accountData VALUES('1','存款',1300)

INSERT INTO accountData VALUES('2','存款',2600)

INSERT INTO accountData VALUES('3','存款',102)

INSERT INTO accountData VALUES ('4', '存款 ',1613030102)

 

 

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

然后查看审计表 wbf_UserTestAccountData 中插入的数据，可以看到，触发器的操作是正确的

--查看结果:

SELECT * FROM wbf_UserTestAccountData

 

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

 

下面是一组删除数据的操作，这些删除操作完成之后，会触发 t_accountData_delete 触发器的执行，应该在 wbf_UserTestAccountData 中记录下来。删除记录的命令如下:

--删除记录的命令如下:

DELETE FROM accountData WHERE accounted = 1

DELETE FROM accountData WHERE accounted = 2

DELETE FROM accountData WHERE accounted = 3

DELETE FROM accountData WHERE accounted = 4

 

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

然后查看审计表 wbf_UserTestAccountData 中插入的数据，可以看到，触发器的操作是正确的

--查看结果:

SELECT * FROM wbf_UserTestAccountData

 

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

 

 

1.示例测试触发器分析

上面的测试仅仅是一次插入、删除一条记录，但是，如果一次插入、删除多条记录，触发器的设计仅考虑记录一行数据，其他插入、删除或更新的记录信息仍然丢掉了，为了验证出现的结果，需要事先删除表 accountData、wbf_UserTestAccountData，再重建这两个表，然后创建插入、删除、更新触发器，在 accountData 表中插入记录，执行下面的命令，一次删除满足 accountID＜ = 4 的多条记录时，就会出现问题，

--命令如下:

DELETE FROM accountData WHERE accounted<=4 SELECT * FROM wbf_UserTestAccountData

 

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

上面的命令一次删除三行记录，结果在 wbf_UserTestAccountData 表中只记录了删除的第一行记录的信息，第二、三两行记录信息丢掉了，导致记录数据的不完整。对于插入、更新触发器，也存在记录数据不完整的情况。

由于存在上面的问题，需要对触发器进行改进，使触发器能够记录插入、更新、删除多条记录的所有行的数据。在触发器执行的时候，会产生两个临时表:inserted 表和 deleted 表，它们的结构和触发器所在的表的结构相同。当一条记录插入表中时，相应的插入触发器创建一个 inserted 表，该表映射了与该触发器相连接的表的列结构，相应的 inserted 表中也包含了插入表中的数据行;当执行一条 delete 语句时，从表中删除的每一行都包含在删除触发器内的 deleted 表中。执行 update 语句时，修改之前的原始的值存入 deleted 表中修改后的新值存入 inserted 表中。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件，然而，不能直接对表中的数据进行更改。

 

根据触发器中的语句所执行的操作类型的不同，执行触发器过程中可以创建一个或者两个临时表(inserted 表和 deleted 表)，下方说明了在进行何种操作时，触发器创建哪些表。

          

触发器类型

 

 

INSERT

需要创建inserted表

不需要创建 deleted表

 

UPDATE

需要创建inserted表

需要创建 deleted表

 

DELETE

不需要创建inserted表

需要创建 deleted表

 

 

向表中插入数据时，INSERT 触发器触发执行。当INSERT 触发器触发时，新的记录增加到触发器表中和inserted 表中。inserted 表是一个逻辑表，保存了所插入记录的拷贝，允许用户参考 INSERT 语句中数据。触发器可以检查 inserted 表，来确定该触发器的操作是否应该执行和如何执行。在 inserted 表中的记录，总是触发器表中一行或多行记录的冗余。当触发一个 DELETE 触发器时，被删除的记录放在一个特殊的 deleted 表中。deleted 表是一个逻辑表，用来保存已经从表中删除的记录。该 deleted 表允许参考原来的 DELETE 语句删除的已经记录在日志中的数据。修改一条记录就等于插入一条新记录同时删除一条旧记录。同样，UPDATE 语句也可以看成是由删除记录的 DELETE 语句和增加记录的 INSERT 语句组成，当在某一个有 UPDATE 触发器表的上面修改记录时，表中原来的记录移动到 deleted 表中，修改过的记录插入到了 inserted 表中。

  1. 优化后的测试触发器

触发器可以检查 deleted 表和 inserted 表，以便确定是否修改了多个行和应该如何执行触发器的操作。下面创建 INSERT、DELETE、UPDATE 触发器时，访问 inserted 表和 deleted 表的数据，将其插入 wbf_UserTestAccountData 表，包括可能的多行数据。

 

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

CREATE TRIGGER t_accountData_insert

ON accountData

WITH ENCRYPTION

FOR INSERT

AS

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'insert',accountAmount FROM inserted

RETURN

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

 

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

CREATE TRIGGER t_accountData_delete

ON accountData

WITH ENCRYPTION

FOR DELETE

AS

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'delete',accountAmount FROM deleted

RETURN

 

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

在数据更新时，将更新前的旧值表示为 update_old_value，将更新后的新值表示为 update_new_value

 

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

CREATE TRIGGER t_accountData_update

ON accountData

WITH ENCRYPTION

FOR UPDATE

AS

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'update_old_value',accountAmount FROM

deleted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'update _ new _ value ',accountAmount

FROM inserted

RETURN

 

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

再次执行一次删除多条记录的命令，可以看到，它一次在 wbf_UserTestAccountData 表中记录了三条删除记录的信息，结果是正确的，达到了设计的目标，

--命令如下:

DELETE FROM accountData WHERE accounted<=4 SELECT * FROM wbf_UserTestAccountData

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

经过上面的改进，所有插入、更新、删除的记录都可以写入 wbf_UserTestAccountData 审计表，丰富了记录的内容，关键是没有发生操作数据丢失的现象。进一步，可以通过编程控制，将 INSERT、DELETE和 UPDATE 三个触发器合并到一起，经过判断 inserted表和 deleted 表中记录数来区别不同的情况，

--程序代码如下:

CREATE TRIGGER t_accountData_idu

ON accountData

FOR INSERT,DELETE,UPDATE

AS

DECLARE @ins_count INTEGER

DECLARE @del_count INTEGER

SELECT @ins_count=count(*) FROM inserted

SELECT @del_count=count(*) FROM deleted

--insertrow

IF @ins_count=1 and @del_count=0

BEGIN

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'insert',accountAmount FROM inserted

END

--deleterow

IF @ins_count=0 AND @del_count=1

BEGIN

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'delete',accountAmount FROM deleted

END

--updaterow

IF @ins_count=@del_count AND @ins_count=1

BEGIN

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'update_old_value',accountAmount FROM deleted

INSERT INTO wbf_UserTestAccountData (wbf_UserTest_log_actionType,wbf_UserTest_log_amount)

SELECT 'update_new_value',accountAmount FROM inserted

END

RETURN

 

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

在上面的代码中，使用变量@ ins_count 统计 inserted 表中的记录数，@ del_count 统计 deleted 表中的记录数，如果@ ins_count=1 并且 @ del_count=0，则说明当前在 accountData 表上进行的是插入记录的操作;如果@ ins_count = 0 并且 @ del_count=1，则说明当前在 accountData 表上进行的是删除记录的操作;如果@[email protected] del_count 并且 @ ins_count=1，则说明当前在 accountData 表上进行的是更新记录的操作，由此可以通过编程将三种触发器区分开来。

  • 测试总结:
  1. accountData ( accounted，accountType，accountAmount) ，数据类型的确定上，INT不需要指定长度，能使用varchar尽量使用varchar。
  2. 即使是插入数据，也要注意，给每个列名插入值，不能空其中一个。
  3. 第一次测试仅仅是一次插入、删除一条记录，但是，如果一次插入、删除多条记录，触发器的设计仅考虑记录一行数据，其他插入、删除或更新的记录信息仍然丢掉了。
  4. 第二次测试需要事先删除表 accountData、wbf_UserTestAccountData，再重建这两个表，然后创建插入、删除、更新触发器，在 accountData 表中插入记录，执行下面的命令。(当时只是执行清除表中数据，最后发现执行命令时，上次执行的数据还存在)。
  5. 创建INSERT触发器脚本命令时，注意检查@与后方值之间不能出现空格。
  6. 在使用@ins_count>0时，出现了在应使用条件的上下文附近中指定了非布尔类型的表达式,最后我换了一种表达式，即@ins_count=1，因为bool值只有0和1。

介绍SQL Server 中触发器的工作机制和分类，结合具体实例，重点论述了 DML 触发器设计的一般步骤，改进了最初的设计，使触发器能够记录发生在表上的所有操作，进一步，将 INSERT、DELETE 和 UPDATE 三个触发器合并到一起，提高了系统性能。