SQL SERVER触发器（附有实例）

legendaryhaha

已于 2023-09-28 18:43:17 修改

阅读量10w+

点赞数 136

分类专栏：数据库文章标签： SQL server触发器

于 2018-05-18 18:23:52 首次发布

本文链接：https://blog.youkuaiyun.com/legendaryhaha/article/details/80368114

版权

数据库专栏收录该内容

26 篇文章

订阅专栏

触发器：即当发生某一事件时，如果满足给定条件，则执行相应的动作。

它的基本架构：

触发器创建语法：

（1）

CREATETRIGGER trigger_name

ON table|view

FOR|AFTER|INSTEADOF [DELETE][,INSERT][,UPDATE]

Sql_statement[…n]

（2）

CREATETRIGGER trigger_name

ON table|view

FOR|AFTER|INSTEADOF [DELETE][,INSERT][,UPDATE]

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。

另外，定义触发器时，系统都都会自动生成两张表，我们是可以直接用的，如下：

如下是实例（都是亲手实践过的）：

可在这里下载数据库文件，下面实例以这个数据库为例子

我想改为0的但改不了，小伙伴可以参照便文章免费下载

也可以用百度网盘了：链接：https://pan.baidu.com/s/1gtlSrhQSBAd1rblrmU3JiA 提取码：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

3.在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，
如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能修改’，
修改失败，用SQL语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果；

createtrigger t2_sc on sc

after update

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)