SQL触发器

<一>触发器是一种特殊类型的存储过程。存储过程是通过存储过程名被调用执行的，而触发器主要是通过事件触发而被执行的。

<二>触发器可通过数据库中的相关表实现级联更改。如果触发器表存在约束，则在instead of 触发器执行后after触发器执行前检查这些约束，如果约束破坏，则回滚instead of触发器操作并不执行after触发器.

<三>SQL Server为每个触发器都创建两个专用表：inserted表和deleted表。这两个表是逻辑表，不能修改，其表结构与被触发器作用的表的结构相同。触发器执行完毕后，与该触发器相关的这两个表会自动删除，

<三>当执行insert语句时，inserted表存放要向表中插入的所有行.

当执行delete语句时，deleted表存放要从表中删除的所有行。

当执行update语句时，相当于先执行一个delete操作，再执行一个insert操作。因此，老行首先移到deleted表中，新行插入激活触发器的表和inserted表。

<四>创建触发器格式：

create trigger trigger_name

on table|view

with encryption

for/after/instead of{[insert][,][update][,][delete]}

not for replication

as

if update/insert

sql statement

go

说明：trigger_name:触发器的名字

table/view:执行触发器的表

with encrytion:加密

for/after(后触发):for与after同义，指定触发器只有在触发触发器的SQL语句中指定的所有操作都成功完成后才激发。所有的引用级联操作和约束必须成功后才能执行该触发器。

instead of(替代触发):执行触发器，而不执行触发触发器的SQL语句。在表或视图上，每个insert、delete、update语句只能定义一个instead of触发器

[insert][,][update][,][delete]:在表或视图上执行哪些数据修改语句时将激活触发器。必须指定一个选项。

[not for replication]:当复制进程更改触发器所涉及的表时，不执行触发器。

<五>触发器的分类：

按照激发触发过程执行的SQL语句不同可以分为：insert触发、delete触发和update触发

按照是否执行触发的SQL语句可以分为后触发(for/after)和替代触发(instead of)

for/after(后触发)：只有在激发触发过程执行的SQL语句执行完毕后，才执行触发过程体

instead of(替代触发)：只执行触发过程，不执行SQL语句

修改触发器：alter trigger

例2 将例1创建的触发器中For Update修改为Instead Of Update并进行比较。

Alter trigger test1

On 销售人员

Instead of update

As

Print ‘记录没有修改！’

--测试

UPDATE 销售人员 SET 性别=‘女’ WHERE 工号=3

比较：用Instead Of时执行触发器，而不执行触发触发器的SQL语句。

<六>例1 创建一后触发器，要求每当在销售人员表中修改数据时，将向客户端显示一条消息“记录已修改！”

--创建触发器

USE market

GO

CREATE TRIGGER Test1

ON 销售人员

FOR UPDATE

AS

PRINT '记录已修改！'

GO

--测试

UPDATE 销售人员 SET 性别=‘女’ WHERE 工号=1

<七>insert 触发器

• 例1：在订单信息表上建立后触发的插入触发器,当用户插入新的订单时,如果订货量大于货品信息表上的库存量,则不能实现插入操作,并给出提示信息

create trigger check_储存量

on 订单信息

for insert

as

declare @goodno int,@ordernum int,@stored int

select @goodno=货品编码,@ordernum=数量 from inserted

select @stored=库存量 from 货品信息 where 编码=@goodno

if @stored<@ordernum

begin

raiserror('订货量超出库存量，不能进行订货！',7,1)

rollback transaction

end

go