正文

触发器1_概念2013-01-26 11:12:00

【评论】 【打印】 【字体: 】 本文链接:http://blog.pfan.cn/edwardguo/54101.html

分享到:

触发器的特征:


1、触发器是在对表进行增、删、改时,自动执行的存储过程。触发器常用于强制业务规则,它是一种高级约束,通过事件进行触发而被执行。

2、触发器是一个特殊的事务单元,可以引用其他表中的列执行特殊的业务规则或数据逻辑关系。当出现错误时,可以执行rollback transaction操作将整个触发器以及触发它的T-SQL语句一并回滚(不需显示声明begin transaction)。


3、每个触发器将用到的两个临时表

   deleted 临时表:用于临时存放被删除的记录行副本(包括delete和update语句所影响的数据行);
                  注意:被删除的记录行,首先从原始表中删除,并保存到触发器表。然后从触发器表中删除,再保存到deleted表。

   inserted临时表:用于临时存放插入的记录行副本(包括insert和update语句所影响的数据行);

   deleted表和inserted表的特征:
   > 这两个表的表结构与该触发器作用的表相同;
   > 这两个表是逻辑表,并且由系统管理;
   > 这两个表是动态驻留在内存中的(不是存储在数据库中),当触发器工作完成后,它们也被删除;
   > 这两个表是只读的,即只能运用select语句查看(用户不能直接更改);

4、所创建的触发器(insert、delete、update)是在原表数据行已经修改完成后再触发。所以,触发器是在约束检查之后才执行。


什么时候使用触发器?

a、实现主外键关系所不能保证的复杂参照完整性数据的一致性。
    不过,通过“级联引用完整性约束”可以更有效地执行这些更改。

b、防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作,并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。
   > 与 CHECK 约束不同(check约束只能引用自身表中的列),DML触发器可以引用其他表中的列
   > 触发器可以完成所有约束的功能,但不一定是最佳方案;
   > 触发器能够使用自定义信息和较为复杂的错误处理

c、DML 触发器可以评估数据修改前后表的状态,并根据该差异采取措施。

d、一个表中的同一个修改语句的DML触发器,允许被多个不同的操作(INSERT、UPDATE 或 DELETE)来响应


触发器的类型:

insert 触发器;(略)
delete 触发器;(略)
update 触发器:在修改表中记录行或某列数据时触发执行;
注意:update(列)函数:实现检测某列是否被修改。

update 更新操作分为两步:
首先,“删除”更改前原有数据行:删除的原有数据行将复制到deleted临时表中;
然后,“插入”更改后的新数据行:插入新数据行到原始表,同时将新数据行保存到inserted临时表和触发器表中;


创建触发器的注意点:
1、create trigger必须是批处理(go)的第一条语句;

2、一个触发器语句只能用到一个表或一个视图中;
   on 表名/ 视图名

3、一个触发器语句可以执行多个操作;
   for delete,insert,update -- 无先后顺序的任意组合

4、建议DML触发器不返回任何结果。这是因为对这些返回结果的特殊处理必须写入每个允许对触发器表进行修改的应用程序中。
     若要防止从 DML 触发器返回任何结果,请不要在触发器定义中包含select语句或变量赋值;
     如果必须在触发器中进行变量赋值,则应该在触发器被触发之前使用set nocount on语句以避免返回任何结果集;

     注意:未来版本的SQL Server 中,将会删除从触发器返回结果集的功能。

5、如果“触发器表”本身也存在约束,则在执行insert、delete、update触发器前,首先会检查“触发器表”上存在的约束。如果不满足约束,则不会执行其insert、delete、update触发器。



查看当前数据库中的所有触发器
select * from sys.triggers


创建临时表 #tableName

create table #tableName



如何使用 SQL Server 触发器


触发器2_初始化环境SQL

初始化环境
复制代码
--------------- 初始化环境 ---------------

create database TriggerDatabase
use TriggerDatabase
go

if exists(select * from sysobjects where name='bank')
  
drop table bank

create table bank -- 账户信息表
(
   userName     
varchar(10) not null--顾客名
   cardID        varchar(10) not null--卡号
   currentMoney  money       not null   --当前余额
)

if exists(select * from sysobjects where name='transInfo')
  
drop table transInfo

create table transInfo --交易信息表
(
   cardID    
varchar(10) not null--卡号
   transType  char(4)     not null--交易类型(存入/支取)
   transMoney money       not null--交易金额
   transDate  datetime    not null   --交易日期
)
go

--------------- 添加约束 ---------------
alter table bank
add constraint CK_currentMoney check(currentMoney>=1);

alter table transInfo
add constraint DF_transDate default(getdate()) for transDate;

alter table transInfo
add constraint CK_transType check(transType in('支取','存入'));

--------------- 添加测试数据 ---------------
/*
张三 1000元 */
insert into bank(userName,cardID,currentMoney)
       
values('张三','1001 0001',1000);
/* 李四 1元 */
insert into bank(userName,cardID,currentMoney)
       
values('李四','1001 0002',1);
/* 张三 支取 200元 */
insert into transInfo(cardID,transType,transMoney)
       
values('1001 0001','支取',200);

--------------- 查看结果 ---------------
select * from bank;
select * from transInfo;
go
复制代码


触发器3_定义触发器的格式

定义触发器的格式
复制代码
-- =============================================
--
Author:        xugang
--
Create date: 2010-2-14
--
Description:    定义触发器的精简格式
--
  [ ]:可选     { }必选
--
=============================================

create trigger [ schema_name. ] -- 触发器所属架构
               trigger_name     -- 触发器名称
on { table | view }       -- 触发器的表或视图
   [ with encryption ]    -- 加密dml触发器定义(后面详解)
{ for | after }
  
/* after:只有在触发它的SQL语句执行成功后才能激发。
             (只能对“表”定义after)
*/
    {
insert,update,delete }
as
   
/* SQL语句... */
go



--查看当前数据库中的所有触发器
select * from sys.triggers
复制代码


触发器4_insert 触发器SQL

insert 触发器
复制代码
------------------ insert 触发器 ------------------
use TriggerDatabase
go
if exists(select * from sysobjects
          
where name='trig_insert_transInfo')
drop trigger trig_insert_transInfo
go

-- create trigger必须是批处理(go)的第一句

create trigger trig_insert_transInfo
on transInfo for insert
as
   
declare @_transType   char(4),  --定义变量
            @_transMoney  money,
           
@_cardID      char(10),
           
@balance      money     --所剩余额

   
-- 从inserted临时表中获取记录值
    select @_transType = transType,
          
@_transMoney = transMoney,
          
@_cardID = cardID
          
from inserted

   
if(@_transType = '支取')
      
update bank set currentMoney=currentMoney-@_transMoney
             
where cardID = @_cardID;
   
else
      
update bank set currentMoney=currentMoney+@_transMoney
             
where cardID = @_cardID;

   
--显示交易金额
    print '交易成功! 交易金额:'
         
+ convert(varchar(20),@_transMoney)

   
--显示所剩余额
    select @balance = currentMoney from bank
          
where cardId = @_cardID

   
print '卡号:'+@_cardID
         
+ ' 余额:'+convert(varchar(20),@balance);
go



------------------ 测试触发器 ------------------

-- delete from transInfo
set nocount on --不显示T-SQL影响的记录行数

insert into transInfo(cardID,transType,transMoney)
            
values('1001 0001','支取',200);
insert into transInfo(cardID,transType,transMoney)
            
values('1001 0001','存入',10000);
--查看结果
select * from bank
select * from transInfo
复制代码


触发器5_delete 触发器SQL

delete 触发器
复制代码

/* 实现: 当清除'交易信息表'的数据时,
         自动备份被清除的数据到backupTable表中
*/

------------------ delete 触发器 ------------------
use TriggerDatabase
go

if exists (select * from sysobjects
          
where name='trig_delete_transInfo')
drop trigger trig_delete_transInfo
go

create trigger trig_delete_transInfo
on transInfo after delete  --  for | after
as
  
print '开始备份数据,请稍后......'
  
-- 如果数据库中,不存在 backupTable 表
   if not exists(select * from sysobjects
                 
where name='backupTable')
     
select * into backupTable from deleted --deleted临时表
   else
     
insert into backupTable select * from deleted
 
  
print '备份成功,备份表 backupTable 中的数据为:'
       
select * from backupTable;
go


------------------ 测试触发器 ------------------
set nocount on

delete from transInfo; --测试

--查看结果
select * from transInfo
select * from backupTable
复制代码


触发器6_update 触发器SQL

update 触发器
复制代码
------------------ update 触发器 ------------------
use TriggerDatabase
go

if exists (select * from sysobjects
          
where name='trig_update_bank')
drop trigger trig_update_bank
go

create trigger trig_update_bank
on bank for update  --在bank表上创建update触发器
as
  
declare @beforeMoney money,
          
@afterMoney  money,
          
@currentTrans money --当前交易金额

  
--从deleted临时表,获取交易前的余额
   select @beforeMoney = currentMoney from deleted;
  
--从inserted临时表,获取交易后的余额
   select @afterMoney = currentMoney from inserted;
  
  
if abs(@afterMoney-@beforeMoney) > 2000
     
begin
       
print '当前交易金额为:' +
             
convert(varchar(20),abs(@afterMoney-@beforeMoney))
       
-- 自定义错误消息
        raiserror('每次交易金额不能超过2000元,交易失败!',16,1)
      
       
rollback transaction --回滚事务,撤销交易!
        /* 注意:
           触发器是一个特殊的事务单元
           不需显示声明begin transaction
       
*/
     
end
go


------------------ 测试触发器 ------------------
set nocount on

--测试1: 在 bank表触发 update触发器
update bank set currentMoney = currentMoney + 25000
      
where cardID = '1001 0001'

--测试2: 通过 transInfo表的 trig_insert_transInfo触发器
--
             间接触发 bank表的 trig_update_bank触发器

insert into transInfo(cardID,transType,transMoney)
            
values('1001 0001','存入',10000);

--查看结果
select * from bank
select * from transInfo
复制代码

阅读(1495) | 评论(0)


版权声明:编程爱好者网站为此博客服务提供商,如本文牵涉到版权问题,编程爱好者网站不承担相关责任,如有版权问题请直接与本文作者联系解决。谢谢!

评论

暂无评论
您需要登录后才能评论,请 登录 或者 注册