数据库管理实务(四) 触发器的实例分析

本文详细介绍了SQL Server的触发器,包括登录触发器、DDL触发器和DML触发器的分类及应用场景。通过实例分析了DML触发器在财务管理中的应用,展示了如何创建和优化触发器,以确保数据完整性和安全性。
摘要由CSDN通过智能技术生成

 触发器的实例分析

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 @i

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值