《SQLServer2000编程之触发器(PPT41页)》由会员分享，可在线阅读，更多相关《SQLServer2000编程之触发器(PPT41页)（40页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2000编程触发器一、 了解触发器-创建帐户信息表bank和交易表transInfoCREATE 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 MONEY
2、NOT NULL, -交易金额 transDate DATETIME NOT NULL, -交易日期)q一个典型的应用：银行的取款系统 /*-添加约束：帐户余额不能少于元，交易日期默认为当天日期-*/ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT(getDate( ) FOR transDateGO/*-插入测试数据：张三开户，开户金额为；李四开户，开户金额-*/INSERT INTO ba
3、nk(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 bankSELECT * FROM transInfoq一个典型的应用：银行的取款系统 帐户信息表bank
4、交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：l它是一种特殊的存储过程 l也具备事务的功能 l它能在多表之间执行特殊的业务规则 张三开户1000元,李四开户1元 张三李四王五赵二王三宋二刘五插入删除触发器触发赵二退休 赵二员工表退休员工表q触发器是在对表进行插入、更新或删除操作时自动执行的存储过程q触发器通常用于强制业务规则q触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 q可执行复杂的SQL语句（if/while/case）q可引用其它表中的列 什么是触发器 在数据库中的相关表上实现级联更改 事务功能，撤销或回滚违反
5、引用完整性的操作，防止非法修改操作 执行比 CHECK 约束更复杂的约束操作 比较数据修改前后的状态大部分触发器提供了引用被修改数据的能力，这样就允许用户在触发器中引用正被修改语句所影响的行 在一张表的同一类型操作（update,insert,delete）上设置多个触发器，从而可以对同样的修改语句执行不同的多种操作。 和特定表关联，自动调用。当试图在某个表插入、更新或删除数据，而在那个表上定义了针对所做动作的触发器，那么触发器会自动执行。 只有表的拥有者才可以在表上创建或删除触发器，这种权限不许转授。 不能再在视图或临时表上创建触发器，但可以在触发器中引用视图或临时表。 是一个事务的部分。如
6、果触发器执行不成功，则整个修改事务回滚。 不像普通的存储过程，触发器不能被直接调用，也不传递或接受参数 当使用约束、规则、默认值就可以实现预定的数据完整性时，应该优先使用前3种措施。DELETE 触发器INSERT 触发器UPDATE 触发器q触发器触发时：q系统自动在内存中创建deleted表或inserted表q只读，不允许修改；触发器执行完成后，自动删除qinserted 表 q临时保存了插入或更新后的记录行 q可以从inserted表中检查插入的数据是否满足业务需求q如果不满足，则向用户报告错误消息，并回滚操作qdeleted 表q临时保存了删除或更新前的记录行 q可以从deleted
7、表中检查被删除的数据是否满足业务需求q如果不满足，则向用户报告错误消息，并回滚操作5.inserted 和deleted inserted 和deleted 表修改操作inserted表deleted表增加(INSERT)记录存放新增的记录-删除(DELETE)记录-存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息q创建触发器的语法： 二、创建触发器CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR/AFTER/INSTEAD OF DELETE, IN
8、SERT, UPDATE AS T-SQL语句GOWITH ENCRYPTION表示加密触发器定义的SQL文本DELETE, INSERT, UPDATE指定触发器的类型创建触发器 FOR 和 AFTER 是完全相等的，创建相同类型的触发器，在INSERT、UPDATE 或 DELETE 语句执行后触发 INSTEAD OF 触发器取消触发动作，执行替代操作 创建触发器后，其信息插入 sysobjects 和 syscomments 系统表中 SQL Server 不允许在触发器中使用下列语句：ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RESI
9、ZE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG1. INSERT触发器transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001 支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 200插入记录行触发insert
10、触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作qINSERT触发器的工作原理：INSERT 触发器示例问题：解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。帐户信息表bank 交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额张三开户1000元,李四开户1元 分析：在交易信息表上创建INSERT触发器 从inserted临时表中获取插入的数据行根据交易类型(transType)字段的值是存入/支取， 增加/减少对应帐户的余额。INSE
11、RT 触发器示例-关键代码-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 FROM inserted IF (type=支取) UPDATE bank SET currentMoney=currentMoney- outMoney WHERE cardID=my
12、CardID ELSE UPDATE bank SET currentMoney = currentMoney + outMoney WHERE cardID=myCardID .GO 从inserted表中获取交易类型、交易金额等根据交易类型，减少或增加对应卡号的余额 INSERT 触发器示例transInfocardIDtransType transMoney1001 00021001 00021001 00021001 0002存入 300存入 5001001 0001支取 2002. DELETE触发器transInfocardIDtransType transMoney1001 00
13、021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作qDELETE触发器的工作原理：问题：当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 。分析：l在交易信息表上创建DELETE触发器 l被删除的数据可以从deleted表中获取DELETE触发器示例从deleted表中获取被删除的交易记录DELETE触发器示例-关键代码-CREATE TRI
14、GGER trig_delete_transInfo ON transInfo FOR DELETE AS print 开始备份数据，请稍后. IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 备份数据成功，备份表中的数据为: SELECT * FROM backupTable GODELETE触发器示例Deleted（更新前的数
15、据）customerNamecardID currentMoney李四1000 0002 1bankcustomerName cardID currentMoney张三 10010001 1000 李四10000002 13. UPDATE触发器删除记录行向deleted表中插入被删除的副本检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作qUPDATE触发器的工作原理：李四 10000002 20001向inserted表中插入被添加的副本Inserted（更新后的数据）customerNamecardID currentMoney李四1000 0002 2000
16、1插入记录行问题：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。 分析：l在bank表上创建UPDATE触发器 l修改前的数据可以从deleted表中获取l修改后的数据可以从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 inserted IF ABS(afterMoney-beforeMoney)20000 BEGIN print 交易金额:+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR (每笔交易不能超过2万元，交易失败,16,1) ROLLBACK TRANSACTION ENDGO从deleted表中获取交易前的余额，从inserted表
