SQLSERVER触发器（附有实例）
触发器：即当发⽣某⼀事件时，如果满⾜给定条件，则执⾏相应的动作。
它的基本架构：
触发器创建语法：
（1）
CREATETRIGGER trigger_name
ON table|view
FOR|AFTER|INSTEADOF [DELETE][,INSERT][,UPDATE]
AS
Sql_statement[…n]
（2）
CREATETRIGGER trigger_name
ON table|view
FOR|AFTER|INSTEADOF [DELETE][,INSERT][,UPDATE]
AS
IFUPDATE(column)
[{AND|OR}UPDATE(COLUMN)][…]
IF(COLUMNS_UPDATED())
Sql_statement[…n]
注：（不同数据库⽀持不同的类型触发器，有些还⽀持before类型触发器，像SQL server 就不⽀持before触发器）SQL Server ⽀持两种类型的触发器AFTER 触发器和INSTEAD OF 触发器，其中、
AFTER 触发器要求只有执⾏某⼀操作ISERT， UPDATE ，DELETE 之后触发器才被触
发。
1）INSTEAD OF 触发器表⽰并不执⾏其所定义的操作INSERT，UPDATE ，DELETE，⽽
仅是执⾏触发器本⾝，既可在表上定义INSTEAD OF 触发器，也可以在视图上定
义INSTEAD OF 触发器。
2）after 触发器（也叫“FOR”触发器）则会在触发 insert、update 或是delect 动作之后执⾏。
触发事件分为三类：UPDATE、DELETE和INSERT。
另外，定义触发器时，系统都都会⾃动⽣成两张表，我们是可以直接⽤的，如下：
如下是实例（都是亲⼿实践过的）：
也可以⽤百度⽹盘了：链接： 提取码：q79b
1.在表Student中建⽴删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student
中的元组学号为s1，则表SC中SNO为s1的元组也要删除；建⽴完触发器后⽤企业管理器删除Student中学号为30的元组，看看表SC中SNO为30的选课记录是否也⼀起删除；
create trigger t_std2 on student
instead of delete
as
begin
declare @id char(5)
select @id=sno from deleted
delete from sc where SNo =@id
delete from student where SNo=@id
end
go
delete from Student where SNo='00002'
/
*2. 在表Course中增加⼀个职业规划选修课，为(005，职业规划,4,0014)，在表SC中建⽴⼀个触发器，实现规定年龄24岁以上（包括24岁）的学⽣才能选修职业规划这门课程，如果年龄⼩于24岁，则输出’年龄⼩于24，不能选修该门课程’，插⼊失败，⽤SQL语句在SC表中分别插⼊(‘00001’,’005’,null)和
(‘00005’,’005’,null)看看结果；**/
create trigger t_sc on sc
for insert
as
begin
declare @id char(5)
select @id=sno from inserted
if((select cno from inserted)='005' and (select sage from student where SNo= @id )<24)
begin
print '年龄⼩于24，不能选修该门课程 '
rollback transaction
end
else
print 'nice!'
end
insert into course values('005','职业规划','4','0014')
insert into sc values('00001','005',null)
insert into sc values('00005','005',null)
select * from sc
go
如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩⽐修改前低，不能修改’，
修改失败，⽤SQL语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果；
createtrigger t2_sc on sc
after update
as
if(update(score))
begin
declare @score1 numeric(3,1),@score2numeric(3,1)
select @score1=score from inserted
select @score2=score from deleted
if(@score1>@score2 )
print 'nice! '
else
update sc
set sc.Score=@score2 from sc,deleted
where sc.SNo=deleted.SNo andsc.CNo=deleted.CNo
print '失败'
end
update sc
setScore=70 where SNo='00001' and CNo='001'
4. 在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和⼯资，
则输出’更新了年龄和⼯资’，如果更新了年龄没有更新⼯资，则输出’更新了年龄’，
如果更新了⼯资⽽没有更新年龄，则输出’更新了⼯资’，创建完后使⽤SQL语句把
tno为001的年龄加1，把tno为002的⼯资加1，把tno为003的年龄和⼯资都加1，看看结果；
create trigger t_teacher on teacher
after update
as
begin
declare @age int,@sal float
select @age=age from deleted
select @sal=sal from deleted
如果有如果
if(@age <> (select age from inserted )and @sal <>(select sal from inserted))
print '更新了年龄和⼯资 '
else if(@age <> (select age from inserted )and @sal =(select sal from inserted))
print '更新了⼯资 '
else if(@age = (select age from inserted )and @sal <>(select sal from inserted))
print '更新了年龄 '
end
update Teacher
set age=age+1 where Tno='0001'**/
/**
5. 在不删除触发器的前提下，使3创建的触发器⽆效；
alter table teacher disable trigger t_teacher**/
/**
6. 创建⼀个名为tri_Delete_C的触发器，要求⾸先判断数据库中是否已经存在名为tri_Delete_C的触发器，如果存在，⾸先删除，再创建，触发器要求删除⼀门课程时候，⾸先判断该课程有否有⼈选，如果有⼈选，则不能删除，并通过测试数据验证该触发器的执⾏情况。**/
if(exists (select * from sysobjects where xtype='tr' and name='tri_Delete_C'))
begin
drop trigger tri_Delete_C
print '已删除'
end
go
create trigger tri_Delete_C on course
for delete
as
begin
if exists (select * from sc inner join deleted on sc.CNo=deleted.CNo)
begin
print '不能删除'
rollback transaction
end
end
go
alter table sc drop constraint FK__sc__CNo__08B54D69
delete course where CNo='001'
alter table sc add constraint FK__sc__CNo__08B54D69 foreign key(cno) references course(cno)
发布评论