SQL Server-触发器

在SQL Server中，触发器同存储过程一样重要，它们都是用Transact-SQL语言所编写的程序。本篇主要介绍触发器的创建、修改、删除、执行的方法。

触发器

触发器(trigger)是一种特殊类型的存储过程，它也是用Transact-SQL语言编写的程序。

存储过程是由用户利用命令EXECUTE执行它；而触发器是在用户要对某一表内的数据做插入(INSERT)、更新(UPDATE)、删除(DELETE)时被触发执行。

通常我们使用触发器来检查用户对数据库表的更新是否合乎整个应用系统的需求，以及是否合乎商业规则以维持表内数据的完整性和正确性。

触发器的作用

(1)触发器可通过数据库中的相关表实现级联更改。

通过级联参照完整性约束可以更有效地执行这些更改。

(2)触发器可以强制比用CHECK约束定义的约束更为复杂的约束。

• 与CHECK约束不同，触发器可以引用其他表中的列。

例如，触发器可以使用另一个表中的SELECT比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。

• 触发器的主要好处在于它们可以包含使用Transact-SQL代码的复杂处理逻辑。

因此，触发器可以支持约束的所有功能。

(3)触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

(4)一个数据库表中的多个同类触发器(INSERT，UPDATE或DELETE)允许采取多个不同的对策以响应同一个修改语句。

触发器类型

• UPDATE
• INSERT
• DELETE

创建触发器的SQL语句

create trigger 触发器名
  on {表名|视图名}
{
{{for|after|instead of} {[insert] [, update] [, delete]}
as
[{if update (列名) [{and|or} update (列名)] [...n]}]
  SQL语句 [...n]}
}

触发器执行的时间

1. 在UPDATE、INSERT、DELETE语句执行后自动触发执行。
2. 对FOR|AFTER选项，触发器表中若定义了约束，则先处理约束，后执行触发器程序。

使用lnserted和Deleted表

触发器语句中可以使用两种特殊的表:Deleted表和Inserted表。这两个表由SQL Server自动创建和管理，并临时驻留在内存中。在触发器中可以使用这两个表测试某些数据修改的效果及设置触发器操作的条件，但不能直接对表中的数据进行更改。

1. Deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并存储到Deleted表中。
2. Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

更新事务类似于在删除之后执行插入：首先旧行被复制到Deleted表中，然后新行被复制到触发器表和 Inserted表中。

 

创建和执行触发器

1.交互式为创建一级联更新触发器

要求：若修改S表中一学生的学号，则SC表中与该学生相关的学号自动修改。

启动SQL Server Management Studio。在“对象资源管理器”中，展开“数据库”→jxsk→“表”→dbo.C对象。右击“触发器”，在打开的快捷菜单中，选择“新建触发器”打开触发器编辑窗口。

将窗口内模板语句修改为下列SQL语句。

set ansi_nulls on
go
set quoted_identifier on
go
create trigger Trigger_S
  on S after update
as
if update (SNO)
  begin
  declare @SNO_new char(2), @SNO_old char(2)
  select @SNO_new = SNO from inserted
  select @SNO_old = SNO from deleted
  update SC set SNO = @SNO_new where SNO = @SNO_old
  end
go

2.交互式创建一限制更新触发器

要求：若修改SC表中一记录的学号，则要检查表S中是否存在与该学号相同的记录，若存在则不许修改，若不存在则可修改。

启动SQL Server Management Studio。在“对象资源管理器”中，展开“数据库”→jxsk→“表”→dbo.SC对象。右击“触发器”，在打开的快捷菜单中，选择“新建触发器”打开触发器编辑窗口。

将窗口内模板语句修改为下列SQL语句。

set ansi_nulls on
go
set quoted_identifier on
go
create trigger Trigger_SC
  on SC after update
as
if update (SNO)
  begin
  declare @SNO_new char(2), @SNO_old char(2), @SNO_CNT bit
  select @SNO_old = SNO from deleted
  select @SNO_CNT = count(*) from S where SNO = @SNO_old
  if @SNO_CNT = 1
    rollback transaction
  end
go

3.用create语句创建一触发器

要求：当插入一个记录或修改成绩时，确保此记录的成绩在0～100分之间。

create trigger Score_sc_tri
  on SC after insert, update
as 
declare @score_read tinyint
select @score_read = score from inserted
if @score_read between 0 and 100
  begin
  print '操作完成!'
  return
  end
print '成绩超出0~100之间！请重新输入。'
rollback transaction
go

insert into SC values('01', 'C6', 190)
go
insert into SC values('01', 'C7', 100)
go
update SC set SCORE = 130 where SNO = '01' and CNO = 'C4'
go
update SC set SCORE = 90 where SNO = '01' and CNO = 'C4'
go

4.用create语句创建一级联删除触发器

要求：通过课程名从C表中删除某课程信息，同时删除SC表中与此课程相关的选课记录。

create trigger Trigger_DC
  on C after delete
as
declare @CNO_del char(2)
select @CNO_del = CNO from deleted
delete from SC where CNO = @CNO_del
go

5.创建参照完整性：级联删除和级联修改触发器。

create trigger Trigger_CST
  on C after delete, update
as
declare @CNO_ins char(2), @CNO_del char(2)
select @CNO_ins = CNO from inserted
select @CNO_del = CNO from deleted
if update (CNO)
  begin
  update SC set CNO = @CNO_ins where CNO = @CNO_del
  update TC set CNO = @CNO_ins where CNO = @CNO_del
  end
else
  begin
  delete from SC where CNO = @CNO_del
  delete from TC where CNO = @CNO_del
  end
go

6.创建一触发器：当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元；从“副教授”晋升为“教授”时，岗位津贴自动增加900元

create trigger Trigger_PROF
  on T after update
as
declare @prof_old char(10), @prof_new char(10), @tno char(2)
select @prof_old = PROF from deleted
select @prof_new = PROF from inserted
select @tno = TNO from deleted
if @prof_old = '讲师' and @prof_new = '副教授'
  begin
  update T set COMM = COMM + 500 where TNO = @tno
  end
if @prof_old = '副教授' and @prof_new = '教授'
  begin
  update T set COMM = COMM + 900 where TNO = @tno
  end
go

　　

修改触发器

1.交互式修改触发器

要求：若删除数据库表S中某一个学生的记录信息，则自动删除SC表中与该学生相关的记录信息。

启动SQL Server Management Studio。在“对象资源管理器”中，展开“数据库”→jxsk→“表”→dbo.S→“触发器”，右击TRIGGER_S，在打开的快捷菜单中，选择“修改”选项，打开触发器修改窗口，该窗口中显示了此触发器的定义。

把窗口中的SQL语句修改成下面的内容，实现功能：若删除数据库表S中某一个学生的记录信息，则自动删除SC表中与该学生相关的记录信息。

set ansi_nulls on
go
set quoted_identifier on
go
alter trigger Trigger_S
  on S after delete
as
declare @SNO_del char(2)
select @SNO_del = SNO from deleted
delete from SC where SNO = @SNO_del
go

2.用alter语句修改触发器

要求：通过课程名从数据库表C中删除某课程信息，同时删除数据库表SC和TC中与此课程相关的记录。

alter trigger Trigger_DC
  on C after delete
as
declare @CNO_del char(2)
select @CNO_del = CNO from deleted
delete from SC where CNO = @CNO_del
delete from TC where CNO = @CNO_del
go

 

删除触发器

1.交互式删除触发器

启动SQL Server Management Studio。在“对象资源管理器”中，选择“数据库”→jxsk→“表”→dbo.S→“触发器”，选择触发器TRIGGER_S。

右击触发器TRIGGER_S，在打开的快捷菜单中选择“删除”选项，弹出“删除对象”对话框。

单击“确定”按钮，触发器TRIGGER_S即被删除。

2.用drop语句删除触发器

drop trigger Trigger_DC
go