SQL SERVER触发器恢复方法、数据库

触发器

触发器是一种特殊类型的存储过程，它不同前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于sql server约束、默认值和规则的完整性检查。

当往某一个表格中插入记录、修改记录或者删除记录时，sql server就会自动执行触发器所定义的sql语句，从而确保对数据的处理必须符合由这些sql语句所定义的规则。在触发器中可以查询其他表格或者包括复杂的sql语句。触发器和引起触发器执行的sql语句被当作一次事务处理，如果这次事务未获得成功，sql server会自动返回该事务执行前的状态。和check约束相比较，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。它的主要优点如下：

(1)触发器是自动的。当对表中的数据作了任何修改(比如手工输入或者应用程序采取的操作)之后立即被激活。

(2)触发器可以通过数据库中的相关表进行层叠更改。如，可以在titles表的title-id列上写入一个删除触发器，以使其他表中的各匹配行采取删除操作。该触发器用title-id列作为惟一键，在titleauthor、sales及roysched表中对各匹配行进行定位。

(3)触发器可以强制限制。这些限制比用check约束所定义的更复杂。与check约束不同的是，触发器可以引用其它表中的列。例如，触发器可以回滚试图对价格低于10美元的书(存储在title表中)应用折扣(存储在discounts表中)的更新。

一、创建触发器

在sql server中，可以使用企业管理器或者transact-sql语句来创建触发器。在创建触发器之前应该考虑以下几个问题：

(1)create trigger语句必须是批处理中的第一个语句。将该批处理中随后的其它所有语句解释为create trigger语句定义的一部分。

(2)创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

(3)触发器为数据库的对象，其名称必须遵循标识符的命名规则。

(4)虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。

(5)虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。有关更多信息，请参见信息架构视图。

(6)在含有用delete或update操作定义的外键的表中，不能定义instead of和instead of update触发器。

(7)虽然truncate table语句类似于没有where子名(用于删除行)的delete语句，但它并不会引发delete触发器，因为truncate table语句没有记录。

(8)writetext语句不会引发insert或update触发器。

(9)当创建一个触发器时必须指定：名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句，有效选项为insert、update或delete，多个数据修改语句可激活同一个触发器，如，触发器可由insert或update语句激活；执行触发器操作的编程语句。

1.使用企业管理器创建触发器

在企业管理器中，展开指定的和数据库项，然后展开其上创建触发器的表所在的数据库，右击该表，选择“所有任务–管理触发器”，在名称文本框中选择新建，输入触发器文本，单击“检查语法”，单击“应用–确定”即可。

2.使用create trigger命令创建触发器

使用create trigger命令可以创建触发器，其中需要指定定义触发器的基表、触发器执行的事件和触发器的所有指令。创建触发器的过程类似于创建存储过程，其语法形式如下：

create trigger trigger_name
on {table | view} [with encryption]
{
{{for | after | instead of}{[delete][,][insert][,][update]}
[with append]
[not for replication]
as
[{if update(column)
[{and | or}update(column)]
[,..n]
| if (columns_updated(){bitwise_operator}updated_bitmask)
{comparison_operator}column_bitmask[,..n]
}]
sql_statement[,..n]
}
}

其中，各参数的说明如下：

• trigger_name：用于指定触发器的名称。其名称在当前数据库中必须是唯一的。

• table | view：用于指定在其上执行触发器的表或视图，有时称为触发器表或触发器视图。

• with encryption：用于加密syscomments表中包含create trigger语句文本的条目。使用此选项可以防止将触发器作为系统复制的一部分发布。

• after：用于指定触发器只有在触发sql语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定for关键字，则after是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义该触发器。

• instead of：用于规定执行的是触发器而不是执行触发sql语句，从而用触发器替代触发语句的操作。在表或视图上，每个insert、update或delete语句最多可以定义一个instead of触发器。然而，可以在每个具有instead of触发器的视图上定义视图。instead of触发器不能在with check option的可更新视图上定义。如果向指定的with check option选项的可更新视图添加instead of触发器，系统将产生一个错误。用户必须用alter view删除该选项后才能定义instead of触发器。

• {[delete][,][insert][,][update]}：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任何的顺序组合这些关键字。如果指定的选项多于一个，需要用逗号分隔。

• with append：用于指定应该添加现有类型的其他触发器。只有当兼容级别（）是65或更低时，才需要使用该可选子名。如果兼容级别是70或更高，则不必使用该子名。

• not for replication：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

• as：触发器要执行的操作。

• sql_statement：触发器的条件和操作。触发器条件指定其他准则，以确定delete、insert或update语句是否导致执行触发器操作。

• if update(column)：用于测试在指定的列上进行的insert或update操作，不能用于delete操作。可以指定多列。因为在on子句中指定了表名，所以在if update子句中的列名前不要包含表名。若要测试在多个列上进行的insert或update操作，请在第一个操作后指定单独的update(column)子句。在insert操作中，if update高尔夫球返回true值，因为这些列插入了显式值或隐性值(null)。

• if (columns_updated())：用于测试是否插入或更新了所涉及的列，仅用于insert或update触发器中。

• bitwise_operator：用于比较运算的位逻辑运算符。

• updated_bitmask：整型位掩码，表示实际更新或插入的列。如，表t1包含列c1，c2，c3，c4和c5。假定表t1上有update触发器，若要检查列c2、c3和c4是否都已更新，则指定值14(01110)；若要检查是否只有列c2已更新，则指定值2(00010)。

• comparison_operator：是比较运算符。使用等号(=)检查updated_bitmask中指定的所有列是否都实际进行了更新。使用大于号(>)检查updated_bitmask中指定的任一列或某些列是否已更新。

• column_bitmask：是要检查列的整型位掩码，用于检查是否已更新或插入了这些列。

当创建触发器时，如果使用了相同名称的触发器，后建立的触发器将会覆盖前面创建的触发器。用户不能在系统表上创建用户自定义的触发器。

例：创建一个触发器，在titles表上创建一个插入、更新类型的触发器。

程序如下：

use pubs
if exists(select name from sysobjects where name=’reminder’ and type=’tr’)
drop trigger reminder
go
create trigger reminder
on titles
for insert,update
as sql_statements
go

注意：有两个表应知道，deleted表存储delete和update所影响的行的副本，inserted表存储insert和update所影响的行的副本。

二、查看、修改和删除触发器

1.查看触发器

如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在sql server中，有多种方法可以查看触发器信息，其中常用的有两种：

(1)使用企业管理器查看触发器信息。

 

(2)使用系统存储过程查看触发器。

系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。

• sp_help：用于查看触发器的一般信息，包括名称、属性、类型和创建时间。

  sp_help ‘触发器名称’

• sp_helptext：用于查看触发器的正文信息。

  sp_helptext ‘触发器名称’

• sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。

  sp_depends ‘触发器名称’
  sp_depends ‘表名’

2.修改触发器

通过企业管理器和系统过程或者transact-sql命令，可以修改触发器的名称和正文。

(1)使用企业管理器修改触发器正文。

 

(2)使用sp_rename命令修改触发器的名称。

语法形式如下：

sp_rename oldname,newname

(3)使用alter trigger命令修改触发器正文。

语法形式如下：

alter trigger trigger_name
on(table | view)
[with encryption]
{
{(for | after | instead of){[delete][,][insert][,][update]}
[not for replication]
as
sql_statement[...n]}
|
{(for | after | instead of){[insert][,][update]}
[not for replication]
as
{if update(column)
[{and | or}update(column)]
[...n]
| if (columns_updated(){bitwise_operator}updated_bitmask)
{comparison_operator}column_bitmask[...n]
}
sql_statement[...n]
}
}

3.删除触发器

由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：

(1)使用系统命令drop trigger删除指定的触发器。其语法形式如下：

drop trigger{trigger}[,..n]

(2)删除触发器所在的表。删除表时，系统将会自动删除与该表相关的触发器。

(3)在企业管理器中，右击要删除的触发器所在的表，从弹出的菜单中依次选择“所有任务–管理触发器”，则会出现触发器属性对话框，在名称选项框中选择要删除的触发器，然后单击“删除”即可。

三、触发器的应用

1.使用insert触发器

insert触发器通常被用于更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准， 以确保数据的完整性。当向数据库中插入数据时，insert触发器将被触发执行。insert触发器被触发时，新的记录增加到触发器对应的表中，并且同时也添加到一个插入表中。该插入表是一个逻辑表，以确定该触发器的操作是否应该执行以及如何去执行。

例：创建一个在表score中检查插入的成绩是否在0到100之间的触发器。

程序如下：

use test
create table score
(
student_no int,
score int
)

if exists(select name from sysobjects where name=’check_score’ and type=’tp’)
drop trigger check_score
go

create trigger check_score
on score
for insert,update
as
declare @score int
select @score=score from inserted
if @score<0 or @score>100
begin
rollback
raiserror(’成绩必须在0到100之间！’,16,1)
end
go

如果此时插入一笔记录：

insert score values(985240,150)

则会出现出错提示。同样在更新记录时，如果修改后的数据不满足要求，也会出现上述错误信息。

2.使用delete触发器

delete触发器通常用于两种情况，第一种情况是为了防止那些确定需要删除但传统引起数据一致性问题的记录的删除，如在雇员信息表中删除记录时，同时要删除和该雇员有关的其他信息表。通常见于那些用作其他表的外部键的记录,。第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。

例：score表中包含学生的学号和成绩，如果还存在一个t1表，其中包含学生的学号和姓名，它们之间以学号相关联。

如果要删除t1表中的记录，则与该记录的学号对应的学生成绩也应该删除：

insert score values(995240,85)
go

create trigger delete_trigger
on t1
for delete
as
delete score where score.student_no=deleted.student_number
go

此时，要删除t1表中的记录：

delete t1 where student_number=995240

则score表中对应的记录也被删除。如果使用select语句来查询score表，将看到该记录已经被删除。

3.使用嵌套的触发器

如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，…，这些触发器就是嵌套触发器。触发器可嵌套至32层，并且可以控制是否可以通过“嵌套触发器”服务器配置选项进行触发器嵌套。如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，则超出嵌套级，而且触发器将终止。在执行过程中，如果一个触发器修改某个表，而这个表已经有其他触发器，这时就要使用嵌套触发器。

设置递归触发器选项的操作步骤如下：

(1)打开企业管理器，展开服务器组，展开服务器；

(2)展开数据库文件夹，右击要更改的数据库，单击“属性”；

(3)单击“选项”标签，如果允许递归触发器，则可以选择“设置”中的“递归触发器”复选框。