SQLSever中的触发器基本语法与作用

什么是触发器？

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

sql server为每个触发器都创建了两个专用表﹕inserted表和deleted表。

触发器通常用于强制业务规则

触发器是一种高级约束，可以定义比用check 约束更为复杂的约束 ：可执行复杂的sql语句（if/while/case）、可引用其它

表中的列

触发器定义在特定的表上，与表相关。

自动触发执行

不能直接调用

是一个事务（可回滚）使用触发器的好处触发器可通过数据库中的相关表实现级联更改，不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用check约束定义的约束更为复杂的约束。与 check 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 select 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（insert、update 或 delete）允许采取多个不同的对策以响应同一个修改语句。

dml触发器分为：

1、 after触发器（之后触发）

a、 insert触发器

b、 update触发器

c、 delete触发器

update 触发器创建触发的语法

create trigger trigger_name
on table_name
[with encryption]
for [delete, insert, update]
as
t-sql语句
go

注意：with encryption表示加密触发器定义的sql文本 delete, insert, update指定触发器的类型 insert 触发器示例

/*创建insert触发器，在上网记录表recordinfo上创建插入触发器*/
create trigger tr_insert_recordinfo
on recordinfo
for insert
as
/*定义变量，用于临时存储插入的会员号、电脑编号和卡的编号*/
declare @cardid char()
declare @pcid int
declare @cardnumber char()
/*从inserted临时表中获取插入的记录行的信息，包括电脑的编号、卡的编号*/
select @pcid=pcid,@cardid=cardid from inserted
/*根据电脑编号修改电脑的使用状态*/
update pcinfo set pcuse= where pcid=@pcid
/*根据卡的编号查询会员号*/
select @cardnumber=cardnumber from cardinfo where cardid=@cardid
/*显示上机成功的信息*/
print '上机成功！会员号是：'+@cardnumber+'机器号是：'+convert(char(),@pcid)
go
----插入测试数据，会员号为的上机
set nocount on --不显示sql语句影响的记录行数
declare @cardid int ---声明一个存储卡的编号的变量
---根据会员号查处卡的编号
select @cardid=cardid from cardinfo where cardnumber='c' 
---向recordinfo表中插入一条记录信息，卡的编号、电脑编号和上机时间
insert into recordinfo(cardid,pcid,begintime) values(@cardid,,getdate())
----查看结果
select * from recordinfo
select * from pcinfo

update触发器示例

---创建delete触发器,在上网记录表recordinfo上创建删除触发器
create trigger tr_delete_recordinfo
on recordinfo
for delete
as
if exists(select * from sysobjects where name='backrecordinfo')
----如果backrecordinfo表存在，就添加记录即可
insert into backrecordinfo select * from deleted
else
----创建backrecordinfo表，从deleted中获取被删除的数据
select * into backrecordinfo from deleted
print'backrecordinfo表备份数据成功，备份表中的数据为：'
select * from backrecordinfo
go
-------关键代码------
----测试delete触发器，删除数据
set nocount on
delete from recordinfo
---查看结果
print'记录表中的数据为：'
select * from recordinfo

delete触发器示例

-------关键代码------
create trigger tr_update_recordinfo
on recordinfo
for update
as
declare @beforepcid int
declare @afterpcid int
select @beforepcid =pcid from deleted
select @afterpcid=pcid from inserted
---根据电脑编号修改使用状态-----
---根据以前使用的电脑编号把电脑的使用状态改为：
update pcinfo set pcuse= where pcid=@beforepcid
---根据现在使用的电脑编号把电脑的使用状态改为:
update pcinfo set pcuse= where pcid=@afterpcid
----显示电脑换机成功
print'换机成功！从'+convert(varchar(),@beforepcid)+'号电脑换到'+convert(varchar(),@afterpcid)+'号电脑'
go
/*测试update触发器，修改电脑编号*/
--显示更改前,记录表中的数据
print'更改前，记录表中的数据'
select * from recordinfo
--显示更改前，电脑表中的数据
print'更改前，电脑表中的数据'
select * from pcinfo
set nocount on
---把电脑号为的改为
update recordinfo set pcid= where pcid= 
---查看结果
print'更改后，记录表中的数据'
select * from recordinfo
print'更改后，电脑表中的数据'
select * from pcinfo

instead of触发器的使用 instead of触发器的使用范围,instead of 触发器可以同时在数据表和视图中使用。通常在以下几种情况下,建议使用instead of触发器: 数据库里的数据禁止修改:例如电信部门的通话记录是不能修改的,一旦修改,则通话费用的计数将不准确。 有可能要回滚修改的sql语句 在视图中使用触发器 用自己的方式去修改数据 instead of触发器示例

---创建update触发器，在上网记录表recordinfo上创建修改（列）触发器
create trigger tr_updatecolum1_recordinfo
on recordinfo
instead of insert
as
declare @cardbalance int --声明用于存储用户余额的变量
declare @cardid int --声明用于存储用户卡的编号的变量
declare @pcid int --声明用于存储电脑编号的变量
---inserted临时表中获取插入的记录行信息，包括电脑编号、卡的编号
select @cardid=cardid,@pcid=pcid from inserted
select @cardbalance=cardbalance from cardinfo where cardid=@cardid
print'您的余额为：'+convert(varchar(10),@cardbalance) ---打印余额信息
if(@cardbalance<2) ---判断余额多少，看能否正常上机
print'余额小于元，不能上机。请尽快充值！'
else
----根据电脑的编号修改电脑的使用状态更改为正在使用
update pcinfo set pcuse=1 where pcid=@pcid
----向recordinfo表插入上机记录
insert into recordinfo(cardid,pcid,begintime)values(@cardid,@pcid,getdate())
print'上机成功'
-------关键代码------
set nocount on
declare @cardid int ---声明一个存储卡的编号的变量
---根据会员号查出卡的编号
select @cardid=cardid from cardinfo where cardnumber='c001'
----向recordinfo表中插入一条记录信息，卡的编号、电脑的编号和上机时间
insert into recordinfo(cardid,pcid,begintime)values(@cardid,1,getdate())
select * from recordinfo
select * from pcinfo

以上所述是www.887551.com给大家介绍的sqlsever中的触发器基本语法与作用的相关内容，希望对大家有所帮助！