《SQL Server 2000编程之触发器》由会员分享，可在线阅读，更多相关《SQL Server 2000编程之触发器（40页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2000编程触发器,一、 了解触发器,-创建帐户信息表bank和交易表transInfo CREATE TABLE bank -帐户信息表 ( customerName CHAR(8) NOT NULL, -顾客姓名 cardID CHAR(10) NOT NULL , -卡号 currentMoney MONEY NOT NULL -当前余额 ) CREATE TABLE transInfo -交易信息表 ( cardID CHAR(10) NOT NULL, -卡号 transType CHAR(4) NOT NULL, -交易类型（存入/支取） transMoney
2、 MONEY NOT NULL, -交易金额 transDate DATETIME NOT NULL, -交易日期 ),一个典型的应用：银行的取款系统,/*-添加约束：帐户余额不能少于元，交易日期默认为当天日期-*/ ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1) ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT(getDate( ) FOR transDate GO,/*-插入测试数据：张三开户，开户金额为；李四开户，开户金额-*/ I
3、NSERT INTO bank(customerName,cardID,currentMoney) VALUES(张三,1001 0001,1000) INSERT INTO bank(customerName,cardID,currentMoney) VALUES(李四,1001 0002,1) /*-插入测试数据：张三取钱-*/ INSERT INTO transInfo(cardID,transType,transMoney) VALUES(1001 0001,支取,200) -查看结果 SELECT * FROM bank SELECT * FROM transInfo,一个典型的应用
4、：银行的取款系统,帐户信息表bank,交易信息表transInfo,张三取钱200 问题： 没有自动修改张三的余额,最优的解决方案就是采用触发器： 它是一种特殊的存储过程 也具备事务的功能 它能在多表之间执行特殊的业务规则,张三开户1000元,李四开户1元,赵二,插入,1.什么是触发器,删除,触发器触发,赵二退休,赵二,员工表,退休员工表,触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通常用于强制业务规则 触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case） 可引用其它表中的列,什么是触发器,2.触发器主要提
5、供的功能,在数据库中的相关表上实现级联更改 事务功能，撤销或回滚违反引用完整性的操作，防止非法修改操作 执行比 CHECK 约束更复杂的约束操作 比较数据修改前后的状态 大部分触发器提供了引用被修改数据的能力，这样就允许用户在触发器中引用正被修改语句所影响的行 在一张表的同一类型操作（update,insert,delete）上设置多个触发器，从而可以对同样的修改语句执行不同的多种操作。,3.使用触发器的说明,和特定表关联，自动调用。 当试图在某个表插入、更新或删除数据，而在那个表上定义了针对所做动作的触发器，那么触发器会自动执行。 只有表的拥有者才可以在表上创建或删除触发器，这种权限不许转授
6、。 不能再在视图或临时表上创建触发器，但可以在触发器中引用视图或临时表。 是一个事务的部分。如果触发器执行不成功，则整个修改事务回滚。 不像普通的存储过程，触发器不能被直接调用，也不传递或接受参数 当使用约束、规则、默认值就可以实现预定的数据完整性时，应该优先使用前3种措施。,4.触发器的类型,DELETE 触发器 INSERT 触发器 UPDATE 触发器,触发器触发时： 系统自动在内存中创建deleted表或inserted表 只读，不允许修改；触发器执行完成后，自动删除 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不
7、满足，则向用户报告错误消息，并回滚操作 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚操作,5.inserted 和deleted,inserted 和deleted 表,inserted表和deleted表存放的信息,创建触发器的语法：,二、创建触发器,CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR/AFTER/INSTEAD OF DELETE, INSERT, UPDATE AS T-SQL语句 GO,WITH
8、 ENCRYPTION表示加密触发器定义的SQL文本 DELETE, INSERT, UPDATE指定触发器的类型,创建触发器,FOR 和 AFTER 是完全相等的，创建相同类型的触发器，在INSERT、UPDATE 或 DELETE 语句执行后触发 INSTEAD OF 触发器取消触发动作，执行替代操作 创建触发器后，其信息插入 sysobjects 和 syscomments 系统表中 SQL Server 不允许在触发器中使用下列语句： ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RESIZE、DROP DATABASE、LOAD DATA
9、BASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG,1. INSERT触发器,插入记录行,触发insert触发器。向inserted表中插入新行的副本,触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作,INSERT触发器的工作原理：,INSERT 触发器示例,问题： 解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。,帐户信息表bank,交易信息表transInfo,张三取钱200 问题： 没有自动修改张三的余额,张三开户1000元,李四开户1元,
10、分析： 在交易信息表上创建INSERT触发器 从inserted临时表中获取插入的数据行 根据交易类型(transType)字段的值是存入/支取， 增加/减少对应帐户的余额。,INSERT 触发器示例-关键代码-,CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney， myCardID=cardID F
11、ROM inserted IF (type=支取) UPDATE bank SET currentMoney=currentMoney- outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney = currentMoney + outMoney WHERE cardID=myCardID . GO,从inserted表中获取交易类型、交易金额等,根据交易类型，减少或增加对应卡号的余额,INSERT 触发器示例,2. DELETE触发器,删除记录行,触发delete触发器向deleted表中插入被删除的副本,触发器检查de
12、leted表中被删除的数据，决定是否需要回滚或执行其他操作,DELETE触发器的工作原理：,问题： 当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 。,分析： 在交易信息表上创建DELETE触发器 被删除的数据可以从deleted表中获取,DELETE触发器示例,从deleted表中获取被删除的交易记录,DELETE触发器示例-关键代码-,CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print 开始备份数据，请稍后. IF NOT EXISTS(SELECT * FROM sysobj
13、ects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 备份数据成功，备份表中的数据为: SELECT * FROM backupTable GO,DELETE触发器示例,3. UPDATE触发器,删除记录行,向deleted表中插入被删除的副本,检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作,UPDATE触发器的工作原理：,向inserted表中插入被添加的副本,插入记
14、录行,问题： 跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。,分析： 在bank表上创建UPDATE触发器 修改前的数据可以从deleted表中获取 修改后的数据可以从inserted表中获取,UPDATE触发器示例,CREATE TRIGGER trig_update_bank ON bank FOR UPDATE AS DECLARE beforeMoney MONEY,afterMoney MONEY SELECT beforeMoney=currentMoney FROM deleted SELECT afterMoney=currentMoney FROM i
15、nserted IF ABS(afterMoney-beforeMoney)20000 BEGIN print 交易金额:+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR (每笔交易不能超过2万元，交易失败,16,1) ROLLBACK TRANSACTION END GO,从deleted表中获取交易前的余额，从inserted表中获取交易后的余额,交易金额是否2万,回滚事务，撤销交易,UPDATE触发器-关键代码-,UPDATE触发器,列级 UPDATE 触发器,UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是
16、否修改了某列的数据 使用UPDATE（列）函数检测是否修改了某列,问题： 交易日期一般由系统自动产生，默认为当前日期。为了安全 起见，一般禁止修改，以防舞弊。,分析： UPDATE（列名）函数可以检测是否修改了某列,-关键代码- CREATE TRIGGER trig_update_transInfo ON transInfo FOR UPDATE AS IF UPDATE(transDate) BEGIN print 交易失败. RAISERROR (安全警告：交易日期不能修改， 由系统自动产生,16,1) ROLLBACK TRANSACTION END GO,检查是否修改了交易日期列transD
