SqlServer 触发器

对表的操作

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,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触发器被触发',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触发器被触发',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有问题',1);
??????? rollbacktran;
??? end
go
--test
insert into studentvalues('forest',2,7);
insert into studentvalues('forest',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,6);
update student set sex = 0 where name ='xiaoming';
delete student where name = 'xiaoming';
select * from log;
select * from student orderby id;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!