SqlServer 触发器

SqlServer触发器

一．触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束

二．SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。 Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

对表的操作

Inserted逻辑表

Deleted逻辑表

Insert

存放增加的记录

无

Delete

无

存放删除后的记录

Update

存放更新后的记录

存放更新前的记录

Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

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

三．Instead of 和 After触发器

SQL Server提供了两种触发器﹕Instead of 和After 触发器。

这两种触发器的差别在于他们被激活的不同﹕

Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。       

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。 After触发器只能用于表。一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。

四．触发器的执行过程 如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。      

Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立﹐其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。

五．创建触发器

 语法：

createtrigger tgr_name
on table_name
with encrypion –-加密触发器
    forupdate...
as
    Transact-SQL

例1. 创建insert类型触发器

--创建insert插入类型触发器
if (object_id('Test_insert','tr')isnotnull)
    droptrigger Test_insert
go
createtrigger Test_insert
on classes
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @tempint;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = namefrom inserted;
    set @name = @name +convert(varchar, @id);
    set @temp = @id / 2;   
    insert into studentvalues(@name, 18 + @id,@temp, @id);
    print'添加学生成功！';
go
--插入数据
insert into classesvalues('5班', getDate());
--查询数据
select * from classes;
select * from student orderby id;

insert触发器，会在inserted表中添加一条刚插入的记录。  

 例2. 创建delete类型触发器

--delete删除类型触发器
if (object_id('Test_delete','TR')isnotnull)
    droptrigger Test_delete
go
createtrigger Test_delete
on classes
    fordelete --删除触发
as
    print'备份数据中……';   
    if (object_id('classesBackup','U')isnotnull)
        --存在classesBackup，直接插入数据
        insert into classesBackupselect name, createDatefrom deleted;
    else
        --不存在classesBackup创建再插入
        select *into classesBackupfrom deleted;
    print'备份数据成功！';
go
--不显示影响行数
--set nocount on;
delete classes where name = '5班';
--查询数据
select * from classes;
select * from classesBackup;

delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。

例3.创建update类型触发器

--update更新类型触发器
if (object_id('Test_update','TR')isnotnull)
    droptrigger Test_update
go
createtrigger Test_update
on classes
    forupdate
as
    declare @oldNamevarchar(20), @newNamevarchar(20);
    --更新前的数据
    select @oldName = namefrom deleted;
    if (exists (select *from student where namelike'%'+ @oldName +'%'))
        begin
            --更新后的数据
            select @newName = namefrom inserted;
            update studentset name = replace(name, @oldName, @newName)where name like'%'+ @oldName +'%';
            print'级联修改数据成功！';
        end
    else
        print'无需修改student表！';
go
--查询数据
select * from student orderby id;
select * from classes;
update classes set name = '五班'where name ='5班';

update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。  

例4.update更新列级触发器

if (object_id('Test_update_column','TR')isnotnull)
    droptrigger Test_update_column
go
createtrigger Test_update_column
on classes
    forupdate
as
    --列级触发器：是否更新了班级创建时间
    if (update(createDate))
    begin
        raisError('系统提示：班级创建时间不能修改！', 16, 11);
        rollbacktran;
    end
go
--测试
select * from student orderby id;
select * from classes;
update classes set createDate = getDate()where id = 3;
update classes set name = '四班'where id = 7;

更新列级触发器可以用update是否判断更新列记录；

例5.instead of类型触发器

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

创建语法

createtrigger tgr_name
on table_name
with encryption
    instead ofupdate...
as
    T-SQL   

例6.创建instead of触发器

if (object_id('Test_inteadOf','TR')isnotnull)
    droptrigger Test_inteadOf
go
createtrigger Test_inteadOf
on classes
    instead ofdelete/*, update,insert*/
as
    declare @id int, @name varchar(20);
    --查询被删除的信息，病赋值
    select @id = id, @name = namefrom deleted;
    print'id: ' +convert(varchar, @id) +', name: ' + @name;
    --先删除student的信息
    delete studentwhere cid = @id;
    --再删除classes的信息
    delete classeswhere id = @id;
    print'删除[ id: ' +convert(varchar, @id) +', name: ' + @name +' ]的信息成功！';
go
--test
select * from student orderby id;
select * from classes;
delete classes where id = 7;    

例7.显示自定义消息raiserror

if (object_id('tgr_message','TR')isnotnull)
    droptrigger tgr_message
go
createtrigger tgr_message
on student
    after insert,update
asraisError('tgr_message触发器被触发', 16, 10);
go
--test
insert into studentvalues('lily', 22, 1, 7);
update student set sex = 0 where name ='lucy';
select * from student orderby id;

例8. 修改触发器

altertrigger tgr_message
on student
afterdelete
asraisError('tgr_message触发器被触发', 16, 10);
go
--test
deletefrom studentwhere name ='lucy';

例9.启用、禁用触发器

--禁用触发器
disable trigger tgr_messageon student;
--启用触发器
enable trigger tgr_messageon student;

例10.查询创建的触发器信息

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

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

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

例11.示例，验证插入数据

if ((object_id('tgr_valid_data','TR')isnotnull))
    droptrigger tgr_valid_data
go
createtrigger tgr_valid_data
on student
after insert
as
    declare @ageint,
            @name varchar(20);
    select @name = s.name, @age = s.agefrom inserted s;
    if (@age < 18)
    begin
        raisError('插入新数据的age有问题', 16, 1);
        rollbacktran;
    end
go
--test
insert into studentvalues('forest', 2, 0, 7);
insert into studentvalues('forest', 22, 0, 7);
select * from student orderby id;

例12. 示例，操作日志

if (object_id('log','U')isnotnull)
    droptable log
go
createtable log(
    id intidentity(1, 1)primarykey,
    actionvarchar(20),
    createDate datetime default getDate()
)
go
if (exists (select *from sys.objectswhere name ='tgr_student_log'))
    droptrigger tgr_student_log
go
createtrigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1from inserted)) and (exists (select 1from deleted)))
    begin
        insert into log(action)values('updated');
    end
    elseif (exists (select 1from inserted) andnotexists (select 1from deleted))
    begin
        insert into log(action)values('inserted');
    end
    elseif (notexists (select 1 from inserted)andexists (select 1from deleted))
    begin
        insert into log(action)values('deleted');
    end
go
--test
insert into studentvalues('xiaoming', 21, 1, 6);
update student set sex = 0 where name ='xiaoming';
delete student where name = 'xiaoming';
select * from log;
select * from student orderby id;