SQL Server触发器小说摘录

# 查询创立的触发器新闻

if (object_id(‘tgr_classes_inteadOf’, ‘TR’) is not null) drop trigger
tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes
instead of delete/*, update, insert*/as declare @id int, @name
varchar(20); –查询被去除的音讯,病赋值 select @id = id, @name = name
from deleted; print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name;
–先删除student的音讯 delete student where cid = @id;
–再删除classes的音信 delete classes where id = @id; print ‘删除[ id: ‘

–delete删除类型触发器if (object_id(‘tgr_classes_delete’, ‘TR’) is
not null) drop trigger tgr_classes_deletegocreate trigger
tgr_classes_deleteon classes for delete –删除触发as print
‘备份数据中……’; if (object_id(‘classesBackup’, ‘U’) is not null)
–存在classesBackup,直接插入数据 insert into classesBackup select name,
createDate from deleted; else –不设有classesBackup创造再插入 select *
into classesBackup from deleted; print
‘备份数据成功!’;go—-不出示影响行数–set nocount on;delete classes
where name = ‘5班’;–查询数据select * from classes;select * from
classesBacku

# 创建instead of触发器

create trigger tgr_nameon table_namewith encryption instead of
update…as T-SQL

存放更新前的笔录

# 启用、禁用触发器

# 修改触发器

Update数据的时候就是先删除表记录,然后伸张一条记下。那样在inserted和deleted表就都有update后的数码记录了。注意的是:触发器本身就是一个作业,所以在触发器里面可以对修改数据开展部分出奇的检查。纵然不知足可以动用工作回滚,撤废操作。

对表的操作

c、 delete触发器

  • convert(varchar, @id) + ‘, name: ‘ + @name + ‘ ]
    的新闻成功!’;go–testselect * from student order by id;select * from
    classes;delete classes where id = 7

1、 after触发器(之后触发)

存放被去除的笔录

# 创制delete类型触发器

update触发器会在立异数据后,将履新前的多寡保存在deleted表中,更新后的数据保存在inserted表中。

DML触发器分为:

创办语法

Ø 创立触发器

if (object_id(‘tgr_message’, ‘TR’) is not null) drop trigger
tgr_messagegocreate trigger tgr_messageon student after insert,
updateas raisError(‘tgr_message触发器被触发’, 16, 10);go–testinsert
into student values(‘lily’, 22, 1, 7);update student set sex = 0 where
name = ‘lucy’;select * from student order by id

# 示例,操作日志

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

–查询已存在的触发器select * from sys.triggers;select * from
sys.objects where type = ‘T瑞鹰’;–查看触发器触发事件select te.* from
sys.trigger_events te join sys.triggers ton t.object_id =
te.object_idwhere t.parent_class = 0 and t.name =
‘tgr_valid_data’;–查看创制触发器语句exec sp_helptext ‘tgr_message’

其间after触发器须要只有执行某一操作insert、update、delete之后触发器才被触发,且只可以定义在表上。而instead
of触发器表示并不实施其定义的操作(insert、update、delete)而仅是实施触发器本人。既可以在表上定义instead
of触发器,也可以在视图上定义。

# 展现自定义音讯raiserror

delete触发器会在剔除数据的时候,将刚刚剔除的数码保存在deleted表中。

语法

if (object_id(‘log’, ‘U’) is not null) drop table loggocreate table
log( id int identity(1, 1) primary key, action varchar(20), createDate
datetime default getDate())goif (exists (select * from sys.objects
where name = ‘tgr_student_log’)) drop trigger
tgr_student_loggocreate trigger tgr_student_logon studentafter
insert, update, deleteas 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’); endgo–testinsert into student values(‘king’, 22, 1,
7);update student set sex = 0 where name = ‘king’;delete student where
name = ‘king’;select * from log;select * from student order by id

–禁用触发器disable trigger tgr_message on student;–启用触发器enable
trigger tgr_message on student

触发器有多个奇特的表:插入表(instered表)和删除表(deleted表)。那两张是逻辑表也是虚表。有系列在内存中创设者两张表,不会储存在数据库中。而且两张表的都以只读的,只好读取数据而不可能改改数据。那两张表的结果连续与被改触发器应用的表的布局同样。当触发器达成工作后,那两张表就会被剔除。Inserted表的数量是插入或是修改后的数目,而deleted表的数目是翻新前的只怕删除的数码。

create trigger tgr_nameon table_namewith encrypion –加密触发器 for
update…as Transact-SQL

履新列级触发器可以用update是还是不是认清更新列记录;

存放扩张的记录

if ((object_id(‘tgr_valid_data’, ‘TR’) is not null)) drop trigger
tgr_valid_datagocreate trigger tgr_valid_dataon studentafter
insertas declare @age int, @name varchar(20); select @name = s.name,
@age = s.age from inserted s; if (@age < 18) begin
raisError(‘插入新数据的age有难点’, 16, 1); rollback tran;
endgo–testinsert into student values(‘forest’, 2, 0, 7);insert into
student values(‘forest’, 22, 0, 7);select * from student order by id

# 创制insert类型触发器

触发器对表举办插队、更新、删除的时候会自行执行的特种存储进程。触发器一般用在check约束越来越复杂的封锁方面。触发器和经常的囤积进度的不相同是:触发器是当对某二个表展开操作。诸如:update、insert、delete这几个操作的时候,系统会活动调用执行该表上相应的触发器。SQL
Server
二〇〇六中触发器可以分成两类:DML触发器和DDL触发器,其中DDL触发器它们会潜移默化各类数额定义语言语句而刺激,那个语句有create、alter、drop语句。

、 update触发器

# update更新列级触发器

增加记录(insert)

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

存放更新后的记录

Inserted逻辑表

if (object_id(‘tgr_SQL Server,classes_update_column’, ‘TR’) is not null) drop
trigger tgr_classes_update_columngocreate trigger
tgr_classes_update_columnon classes for updateas
–列级触发器:是或不是更新了班级创制时间 if (update(createDate)) begin
raisError(‘系统提醒:班级创立时间没办法改改!’, 16, 11); rollback tran;
endgo–测试select * from student order by id;select * from
classes;update classes set createDate = getDate() where id = 3;update
classes set name = ‘四班’ where id = 7

# 示例,验证插入数据

alter trigger tgr_messageon studentafter deleteas
raisError(‘tgr_message触发器被触发’, 16, 10);go–testdelete from
student where name = ‘lucy’

删去记录(delete)

# 创制update类型触发器

二, instead of 触发器 (之前接触)

a、 insert触发器

修改记录(update)

Deleted逻辑表

# instead of类型触发器

instead
of触发器表示并不进行其定义的操作(insert、update、delete)而仅是实践触发器本人的内容。

–update更新项目触发器if (object_id(‘tgr_classes_update’, ‘TR’) is
not null) drop trigger tgr_classes_updategocreate trigger
tgr_classes_updateon classes for updateas declare @oldName
varchar(20), @newName varchar(20); –更新前的多寡 select @oldName = name
from deleted; if (exists (select * from student where name like ‘%’+
@oldName + ‘%’)) begin –更新后的数额 select @newName = name from
inserted; update student set name = replace(name, @oldName, @newName)
where name like ‘%’+ @oldName + ‘%’; print ‘级联修改数据成功!’; end
else print ‘无需修改student表!’;go–查询数据select * from student
order by id;select * from classes;update classes set name = ‘五班’
where name = ‘5班’

–创立insert插入类型触发器if (object_id(‘tgr_classes_insert’, ‘tr’)
is not null) drop trigger tgr_classes_insertgocreate trigger
tgr_classes_inserton classes for insert –插入触发as –定义变量
declare @id int, @name varchar(20), @temp int;
–在inserted表中查询已经插入记录消息 select @id = id, @name = name from
inserted; set @name = @name + convert(varchar, @id); set @temp = @id /
2; insert into student values(@name, 18 + @id, @temp, @id); print
‘添加学生成功!’;go–插入数据insert into classes values(‘5班’,
getDate());–查询数据select * from classes;select * from student order
by id

相关文章