SQL SERVER 7 DML触发器 |
您所在的位置:网站首页 › dml触发器类型 › SQL SERVER 7 DML触发器 |
1、什么是触发器
当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表,还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误(例如,磁盘空间不足),则整个事务即自动回滚。 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通常用于强制业务规则 触发器是一种高级约束,可以定义比用CHECK 约束更为复杂的约束 :可执行复杂的SQL语句(if/while/case)、可引用其它表中的列 触发器定义在特定的表上,与表相关。 自动触发执行 不能直接调用 是一个事务(可回滚) 2、触发器的类型Microsoft SQL Server 提供两种主要机制来强制使用业务规则和数据完整性:约束和触发器。触发器为特殊类型的存储过程,可在执行语言事件时自动生效。SQL Server 包括三种常规类型的触发器:DML 触发器、DDL 触发器和登录触发器。 下面讲解的是DML触发器: 分类: DELETE 触发器 INSERT 触发器 UPDATE 触发器 3、inserted 和deleted 表 触发器触发时: 系统自动在内存中创建deleted表或inserted表 只读,不允许修改;触发器执行完成后,自动删除。 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作 deleted 表: 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作 inserted表和deleted表存放的信息。修改操作 inserted表 deleted表 增加(INSERT)记录 存放新增的记录 ------ 删除(DELETE)记录 ----- 存放被删除的记录 修改(UPDATE)记录 存放更新后的记录 存放更新前的记录 4、如何创建触发器 创建触发器的语法CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL语句 GO WITH ENCRYPTION表示加密触发器定义的SQL文本 DELETE, INSERT, UPDATE指定触发器的类型 INSERT 触发器示例 问题: 当会员号为001上机时:向记录信息表(recordInfo)中插入一条上机信息时,我们应根据电脑的编号自动更新电脑的使用状态。 分析: 在记录信息表上创建INSERT触发器 从inserted临时表中获取插入的数据行 根据电脑编号(PCId)修改电脑状态是否为使用状态,修改电脑状态。/*创建insert触发器,在上网记录表recordInfo上创建插入触发器*/ create trigger tr_insert_recordInfo on recordInfo for insert as /*定义变量,用于临时存储插入的会员号、电脑编号和卡的编号*/ declare @cardId char(10) declare @PCId int declare @CardNumber char(10) /*从inserted临时表中获取插入的记录行的信息,包括电脑的编号、卡的编号*/ select @PCId=PCId,@cardId=CardId from inserted /*根据电脑编号修改电脑的使用状态*/ update PCInfo set PCUse=1 where PCId=@PCId /*根据卡的编号查询会员号*/ select @CardNumber=CardNumber from cardinfo where CardId=@cardid /*显示上机成功的信息*/ print '上机成功!会员号是:'+@CardNumber+'机器号是:'+convert(char(10),@PCId) go ----插入测试数据,会员号为的上机 set nocount on --不显示sql语句影响的记录行数 declare @CardId int ---声明一个存储卡的编号的变量 ---根据会员号查处卡的编号 select @cardId=cardid from cardinfo where cardNumber='c001' ---向recordInfo表中插入一条记录信息,卡的编号、电脑编号和上机时间 insert into recordInfo(cardId,PCId,beginTime) values(@cardId,1,getDate()) ----查看结果 select * from recordInfo select * from PCInfo DELETE触发器示例 问题: 当删除记录表时,要求自动备份被删除的数据到表backRecordInfo中 。 分析: 在记录表上创建DELETE触发器 被删除的数据可以从deleted表中获取---创建delete触发器,在上网记录表RecordInfo上创建删除触发器 create trigger tr_delete_recordInfo on recordInfo for delete as
if exists(select * from sysobjects where name='backRecordInfo') ----如果backrecordInfo表存在,就添加记录即可 insert into backRecordInfo select * from deleted else ----创建backRecordInfo表,从deleted中获取被删除的数据 select * into backRecordInfo from deleted print'backRecordInfo表备份数据成功,备份表中的数据为:' select * from backRecordInfo go -------关键代码------ ----测试delete触发器,删除数据 set nocount on delete from recordInfo ---查看结果 print'记录表中的数据为:' select * from recordInfo UPDATE触发器示例 问题: 在网吧计费系统中,有时我们需要对电脑进行换机操作,这样我们就要更新recordInfo(记录表)中的上机记录。 分析: 当一台电脑空闲时,我们需要修改当前电脑的使用状态为0(正常状态),当一台电脑正在使用时,我们需要修改当前电脑的使用状态为1(使用状态);当修改记录表时,要获取电脑的信息。 为记录表创建update触发器,这样就可以同时修改电脑的状态信息。-------关键代码------ create trigger tr_update_recordInfo on recordInfo for update as declare @beforePCId int declare @afterPCId int select @beforePCId =PCId from deleted select @afterPCId=PCID from inserted ---根据电脑编号修改使用状态----- ---根据以前使用的电脑编号把电脑的使用状态改为: update PCInfo set PCUse=0 where PCId=@beforePCId ---根据现在使用的电脑编号把电脑的使用状态改为: update PCInfo set PCUse=1 where PCId=@afterPCId ----显示电脑换机成功 print'换机成功!从'+convert(varchar(10),@beforePCId)+'号电脑换到'+convert(varchar(10),@afterPCId)+'号电脑' go /*测试update触发器,修改电脑编号*/ --显示更改前,记录表中的数据 print'更改前,记录表中的数据' select * from recordInfo
--显示更改前,电脑表中的数据 print'更改前,电脑表中的数据' select * from PCInfo
set nocount on ---把电脑号为1的改为2 update recordInfo set PCId=2 where PCId=1 ---查看结果 print'更改后,记录表中的数据' select * from recordInfo print'更改后,电脑表中的数据' select * from PCInfo 列级 UPDATE 触发器 update触发器无论是对表中的一行或多行,还是一列或多列,都将执行触发器操作。但是在实际需求中,可能只关心对特定列是否被更新。如果特定的列被更新,就执行触发器操作。这可以通过列上的update语法:if update来实现。在同一个触发器的定义语句中,可以使用多条if update 语句来对不同的列的修改执行不同的触发器操作。 例如,在网吧计费系统中上机时间一般由系统自动产生,默认为当前时间。为了防止非法人员有机可乘(作弊等),一般禁止修改。如何防止用户误操作而修改了上机时间,我们可以使用update(列名)函数来检测是否修改了某列。T-SQL 代码如下:-------关键代码------ ---创建update触发器,在上网记录表recordInfo上创建修改(列)触发器 create trigger tr_updateColum_recordInfo on recordInfo for update as ---检查是否修改了上机时间(beginTime) if update(beginTime) begin print'修改失败!' raiserror('安全警告:上机时间不能修改,由系统自动产生',16,1) rollback transaction ----回滚操作,撤销操作 end go -------关键代码------ set nocount on ---把上机时间修改为-5 update recordInfo set beginTime='2010-6-5' 5、instead of触发器的使用 instead of触发器的使用范围,instead of 触发器可以同时在数据表和视图中使用。通常在以下几种情况下,建议使用instead of触发器: 数据库里的数据禁止修改:例如电信部门的通话记录是不能修改的,一旦修改,则通话费用的计数将不准确。 有可能要回滚修改的SQL语句 在视图中使用触发器 用自己的方式去修改数据 instead of触发器的使用 例如,在网吧计费系统中当用户的余额大于2元时才能上机,否则给出提示信息。在recordInfo表上创建instead of触发器,向recordInfo表插入上机信息时执行触发操作。---创建update触发器,在上网记录表recordInfo上创建修改(列)触发器 create trigger tr_updateColum1_recordInfo on recordInfo instead of insert as declare @cardbalance int --声明用于存储用户余额的变量 declare @CardId int --声明用于存储用户卡的编号的变量 declare @PCId int --声明用于存储电脑编号的变量 ---inserted临时表中获取插入的记录行信息,包括电脑编号、卡的编号 select @cardId=cardId,@PCId=PCId from inserted select @cardbalance=cardBalance from cardInfo where CardId=@CardId print'您的余额为:'+convert(varchar(10),@cardBalance) ---打印余额信息 if(@cardBalance |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |