SQL Server触发器

触发器是一种特殊类型的存储过程，它不同于存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。

什么是触发器

    触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。

DML触发器分为：

1.  after触发器（之后触发）
   1.  insert触发器
   2.  update触发器
   3.  delete触发器
2. instead of 触发器 （之前触发）

    其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

    触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

对表的操作	Inserted逻辑表	Deleted逻辑表
增加记录（insert）	存放增加的记录	无
删除记录（delete）	无	存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

    Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

创建触发器

语法

create trigger tgr_name
on table_name
with encrypion –加密触发器
    for update...
as
    Transact-SQL

创建insert类型触发器

if(object_id('tgr_tb1_insert', 'tr') is not null)
    drop trigger tgr_tb1_insert
go
create trigger tgr_tb1_insert
on dbo.tb1
    for insert    --插入时触发
as
    --定义变量
    declare @name varchar(50), @age int;
    --在inserted表中查询已经插入的记录信息
    select @name=name, @age=age from inserted;  
    insert into dbo.tb2
            ( name, age )
    values  ( @name, -- name - varchar(50)
             @age  -- age - int
              )
go

--插入数据
insert into dbo.tb1
        ( name, age )
values  ( '张三', -- name - varchar(50)
          20  -- age - int
          )

创建delete类型触发器

if(object_id('tgr_tb1_delete', 'tr') is not null)
    drop trigger tgr_tb1_delete
go
create trigger tgr_tb1_delete
on dbo.tb1
    for delete    --删除时触发
as
     --从deleted表中查询已经删除的记录信息，表tb3存在就直接插入数据
     if(object_id('tb3', 'U') is not null)
        insert into tb3 select name, age from deleted;
    else
        --不存在就创建表tb3再插入数据  
        select * into tb3 from deleted;  
go

--删除数据
delete from dbo.tb1 where name='张三' and age=20

创建update类型触发器

if(object_id('tgr_tb2_update', 'tr') is not null)
    drop trigger tgr_tb2_update
go
create trigger tgr_tb2_update
on dbo.tb2
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更改前数据
    select @oldName=name from deleted;
    if exists(select * from dbo.tb3 where name like '%'+@oldName+'%')
    begin
        --更改后数据  
        select @newName=name from inserted;
        update dbo.tb3 set name=replace(name, @oldName, @newName)  where name like '%'+@oldName+'%';
    end
go  

--更改数据 
update dbo.tb2 set name='李四' where name='张三'

扩展：replace函数

replace(string_replace1 , string_replace2 , string_replace3)
作用：

    用string_replace3 替换string_replace1 中出现的所有string_replace2

参数解析： string_replace1 待搜索的字符串表达式 string_replace2 待查找的字符串表达式 string_replace3 替换用的字符串表达式 三个参数可以是字符数据或是二进制数据

返回值：
    如果参数是支持的字符数据类型之一，并且在string_replace1 中能够找到string_replace2，那么返回替换后的字符串；反之， 返回 string_replace1；
    如果参数是支持的 binary 数据类型之一，则返回二进制数据；
    如果有某个参数为NULL，此函数返回NULL 

示例：
1、字符串类型参数：
    select replace('abcdefg bcd','bcd','xxx')
    结果为：axxxefg xxx

2、二进制类型参数：
    select replace(100111001101,111,000)
    结果为：1000001101

update更新列级触发器

if(object_id('tgr_tb2_update_column', 'tr') is not null)
    drop trigger tgr_tb2_update_column
go
create trigger tgr_tb2_update_column
on dbo.tb2
    for update
as
    if(update(age))    --监视指定列的数据更新的触发器
    begin  
        raiserror('系统提示：年龄不能修改', 16, 11);
        rollback tran;
    end
go  

--更改数据
update dbo.tb2 set age='30' where name='李四'

 

 扩展：raiserror 函数

raiserror ( { msg_id | msg_str | @local_variable }        
            { ,severity ,state }        
            [ ,argument [ ,...n ] ] 
          )       
   [ with option [ ,...n ] ]
作用：
      用于抛出一个错误        
          
第一个参数：{ msg_id | msg_str | @local_variable }
      msg_id：表示可以是一个sys.messages表中定义的消息代号；
              使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。
              用户定义错误消息的错误号应当大于 50000。

     msg_str：表示也可以是一个用户定义消息，该错误消息最长可以有 2047 个字符；
             （如果是常量，请使用N'xxxx'，因为是nvarchar的）
              当指定 msg_str 时，RAISERROR 将引发一个错误号为 5000 的错误消息。

     @local_variable：表示也可以是按照 msg_str 方式的格式化字符串变量。
            
第二个参数：severity
            用户定义的与该消息关联的严重级别。（这个很重要）
            任何用户都可以指定 0 到 18 之间的严重级别。
            [0,10]的闭区间内，不会跳到catch；
            如果是[11,19],则跳到catch；
            如果[20,无穷)，则直接终止数据库连接；

第三个参数：state
            如果在多个位置引发相同的用户定义错误，
            则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。
          
            介于 1 至 127 之间的任意整数。（state 默认值为1）
            当state 值为 0 或大于 127 时会生成错误！

第四个参数：argument
            用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。

第五个参数：option
            错误的自定义选项，可以是下表中的任一值：
            LOG ：在错误日志和应用程序日志中记录错误；
            NOWAIT：将消息立即发送给客户端；
            SETERROR：将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000；

instead of类型触发器

instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容。

语法

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    T-SQL

创建instead of触发器

if(object_id('tgr_tb2_inteadOf', 'tr') is not null)
    drop trigger tgr_tb2_inteadOf
go
create trigger tgr_tb2_inteadOf
on dbo.tb2
    instead of delete        --update, insert
as
    declare @name varchar(50), @age int;
    --查询被删除的信息，并赋值
    select @name=name, @age=age from deleted;   
    delete from dbo.tb3 where name=@name and age=@age;
    delete from dbo.tb2 where name=@name and age=@age;
go

--删除数据
delete from tb2 where name='李四' and age=20

显示自定义消息raiserror

if(object_id('tgr_message', 'tr') is not null)
    drop trigger tgr_message
go
create trigger tgr_message
on dbo.tb1 
    after insert, update
as raiserror('tgr_message触发器被触发', 16, 10);
go  

insert into dbo.tb1
        ( name, age )
values  ( '小明', -- name - varchar(50)
          23  -- age - int
          )
update dbo.tb1 set age=18 where name='小明'

示例：验证插入数据

if(object_id('tgr_valid_data', 'tr') is not null)
    drop trigger tgr_valid_data
go 
create trigger tgr_valid_data
on dbo.tb1
after insert
as
    declare @name varchar(20), @age int;
    select @name = s.name, @age = s.age from inserted s;
    if (@age < 18)
    begin
        raisError('插入新数据的age有问题', 16, 1);
        rollback tran;
    end
go

--插入数据
insert into dbo.tb1
        ( name, age )
values  ( '小李', -- name - varchar(50)
          30  -- age - int
          )
insert into dbo.tb1
        ( name, age )
values  ( '小王', -- name - varchar(50)
          2  -- age - int
          )

 

示例：操作日志

if (object_id('log', 'U') is not null)
    drop table log
go
create table log(
    id int identity(1, 1) primary key,
    action varchar(20),
    createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_tb1_log'))
    drop trigger tgr_tb1_log
go
create trigger tgr_tb1_log
on tb1
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log(action) values('updated');
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log(action) values('inserted');
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log(action) values('deleted');
    end
go

--测试数据
insert into dbo.tb1
        ( name, age )
values  ( '小刚', -- name - varchar(50)
          22  -- age - int
          )
update dbo.tb1 set name='张三', age=36 where name='小明'
delete tb1 where name = '小李';

修改触发器

--关键字alter
alter trigger tgr_message
on dbo.tb1 
    after delete
as raiserror('tgr_message触发器被触发', 16, 10);
go  

启用、禁用触发器

--禁用触发器
disable trigger dbo.tgr_message on dbo.tb1
--启用触发器
enable trigger dbo.tgr_message on dbo.tb1

查询创建的触发器信息

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';

--查看触发器触发事件
select te.*,t.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

--查看创建触发器语句
exec sp_helptext 'tgr_message';

 

 

原文链接：https://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html