--触发器--

定义触发器的格式

create trigger <触发器名>

on <表名>

{for|after} <操作时机>

insert,update,delete <触发事件>

as

sql语句

 

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

触发器里面的两个临时表:

Deleted,Inserted分别表示触发事件的表的"旧记录"和“新记录”

 

理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。

    一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：
                            虚拟表Inserted                    虚拟表Deleted

在表记录新增时    存放新增的记录                        不存储记录
        修改时          存放用来更新的新记录                  存放更新前的记录
        删除时          不存储记录                            存放被删除的记录

 

2:Instead of 和 After触发器
SQL Server2000提供了两种触发器: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 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。

 

定义一个出发器,当在表News中插入一条记录后，在表TUsers中插入一条记录

create trigger insertTSPersons

on News

after insert

as

begin

insert into TUsers(UserName,UserPwd,UserRegisterDate) values('wangwu','123456',getdate())

end

在表student表上定义一个出发器，当插入或修改的时候，年龄低于20岁的，自动改为20岁。

create trigger insert_or_update_student

on student

for insert,update/*触发事件是插入或更新操作*/

as/*定义触发动作体*/

update student set sage=20

from student t,inserted i

where t.sno=i.sno and i.sage<20

 

定义触发器，当student学号发生修改时，自动在变化表student_log登记修改记录

create trigger update_s

on student

for update as

if update(sno)

begin

insert into student_log (sno) select sno from deleted

insert into student_log (sno) select sno from inserted

end;

 

create trigger insert_s

on student

for insert

as

insert student_log (sname) select sname from inserted

触发器实例：

例如如果有学生表与班级表，当班级编号编号的时候，学生所在的班级编号也发生变化。

create trigger trustudent

on grade    --在grade表中创建触发器

for update  --为什么事件触发

as         --事件触发后所做的事情

if　update(gradeid)

begin

update student set gradeid=i.gradeid

from student s,Deleted d,inserted i

where s.gradeid=d.gradeid

end

 

实例2：有班级与学生表，如果删除了班级，则将在此班级的所有学生删除

create trigger trdstudent

on grade

for delete

as

delete student

from student s,deleted d

where s.gradeid=d.gradeid

 

实例2:如果数据不符合要求，则数据回滚

1:在orders表中建立触发器，当向orders表中查入一条订单记录数，检查goods表的货物状态是否为1(正在整理),是，则不能往orders表家入改订单。

create trigger  ordergoodinsert

on orders

after insert

as

if(select status from goods,inserted where goods.name=inserted.goodsname)=1

begin

print 'the goods is being processed'

print 'the order cannot be commited'

rollback transaction   --数据回滚

end

 

4、在orders 表建立一个插入触发器，在添加一条订单时，减少goods表相应的货品记录中的库存

create trigger orderinsert

on orders

after insert

as

update goods set storage=storage-inserted.quantity

from goods,inserted

where

goods.name=inserted.goodsname

5、在goods表建立删除触发器，实现goods表和order表的级联删除

create trigger goodsdelete

on goods

after delete

as

delete from orders where goodsname in (select name from deleted)

6、在orders 表建立一个更新触发器，监视orders表的订单日期(orderDate)列，使其不能手工修改

create trigger orderdateupdate

on orders

after update

as

if update(orderdate)

begin

raiserror('orderdate cannot be modified',10,1)

rollback transaction

end

 

Insert of 触发器

　　字面意思为取代,难道说当在一张表上定义了这样的触发器后，对表所做的INSERT、UPDATE、DELETE操作会被替换掉而不执行了？呵呵…这种触发器执行过程为：

　　当对表执行INSERT等操作时，并不直接执行这些操作而是转到触发器里面来执行触发器所定义的操作语句(应该说是一起执行的更合适)；

 

　　演示仍然为上面的所创建的表首先我们演示一个级联删除，当我们在Student表中删除一个被Book引用记录时因为有完整无缺约束我们无法删除这是INSTEAD OF就有作用了　

 

首先建立两个表

--学生
create table Student
(
 StudentNo int primary key, 
 StudentName varchar(20) not null
)
--书本
create table Book
(
 BookId int identity(1,1),
 BookName varchar(30),
 Owner int foreign key references Student(StudentNo)
)
 

create trigger dtStudent

on student

instead of delete

as

begin

declare @studentNo int

select @studentNo=StudentNo from Deleted

delete Book where Owner=@studentNo

end

 

现在演示一个update的Instead of触发器的操作

create trigger utStudent

on Student

instead of Update

as

begin

declare @studentNo int,

@studentNoId int

if update(StudentNo)

begin

select @studentNoId=StudentNo from Deleted

select @studentNo=StudentNo from Inserted

update Book set=Owner=@studentNo where Owner=@studentNoId

end

end