sql触发器

 触发器执行
如果发生约束侵犯，则永远不会执行 AFTER 触发器，因此这些触发器不能用于任何可能防止约束侵犯的处理。

执行 INSTEAD OF 触发器而不是通常的触发动作。在创建将更改反映到基表的 inserted 和 deleted 表之后，但在执行任何操作之前执行这些触发器。这些触发器在执行任何约束前执行，因此可执行预处理以补充约束操作。

如果某个表上定义的 INSTEAD OF 触发器对该表执行一个通常将再次激发 INSTEAD OF 触发器的语句，则将不递归调用该触发器。相反，将视该表为没有 INSTEAD OF 触发器来处理该语句，并启动约束操作与 AFTER 触发器执行之间的链。例如，如果将触发器定义为一个表的 INSTEAD OF INSERT 触发器，并且该触发器在同一表上执行 INSERT 语句，则由 INSTEAD OF 触发器执行的 INSERT 语句不会再次调用该触发器。该触发器执行的 INSERT 将启动这样的进程，执行约束操作并激发为表定义的任何 AFTER INSERT 触发器。

如果视图上定义的 INSTEAD OF 触发器对该视图执行一个通常将再次激发 INSTEAD OF 触发器的语句，则将不递归调用该触发器。而是将该语句解析为对视图的基础基表的更改。在这种情况下，视图定义必须满足对可更新视图的所有限制。有关可更新视图的定义，请参见通过视图修改数据。例如，如果将触发器定义为某个视图的 INSTEAD OF UPDATE 触发器，并且该触发器执行引用该视图的 UPDATE 语句，则由 INSTEAD OF 触发器执行的 UPDATE 语句不会再次调用该触发器。在处理该触发器对视图执行的 UPDATE 语句时，将视图看作没有 INSTEAD OF 触发器一样对待。UPDATE 更改的列必须解析到单个基表。对基础基表所做的每个修改将启动应用约束和激发为表定义的 AFTER 触发器之间的链。

触发器的性能开销通常很低。运行触发器的时间大部分都用于引用其它表，这些表可以在内存或数据库设备上。Deleted 和 inserted 表总是在内存中。触发器所引用的其它表的位置确定操作所需的时间。

 

说明  由于存在对性能的潜在负面影响，不推荐在触发器中使用游标。使用基于行集的逻辑而非游标来设计影响多行的触发器。

创建和维护数据库

使用 inserted 和 deleted 表
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft® SQL Server™ 2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。

inserted 和 deleted 表主要用于触发器中：

扩展表间引用完整性。

在以视图为基础的基表中插入或更新数据。

检查错误并基于错误采取行动。

找到数据修改前后表状态的差异，并基于此差异采取行动。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

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

在设置触发器条件时，应当为引发触发器的操作恰当使用 inserted 和 deleted 表。虽然在测试 INSERT 时引用 deleted 表或在测试 DELETE 时引用 inserted 表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。

 

说明  如果触发器操作取决于一个数据修改所影响的行数，应该为多行数据修改（基于 SELECT 语句的 INSERT、DELETE 或 UPDATE）使用测试（如检查 @@ROWCOUNT），然后采取相应的对策。

SQL Server™ 2000 不允许 AFTER 触发器引用 inserted 和 deleted 表中的 text、ntext 或 image 列；然而，允许 INSTEAD OF 触发器引用这些列。有关更多信息，请参见 CREATE TRIGGER。

在 INSTEAD OF 触发器中使用 inserted 和 deleted 表
传递到在表上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表遵从与传递到 AFTER 触发器的 inserted 和 deleted 表相同的规则。inserted 和 deleted 表的格式与在其上定义 INSTEAD OF 触发器的表的格式相同。inserted 和 deleted 表中的每一列都直接映射到基表中的列。

有关引用带 INSTEAD OF 触发器的表的 INSERT 或 UPDATE 语句何时必须提供列值的规则与表没有 INSTEAD OF 触发器时相同：
不能为计算列或具有 timestamp 数据类型的列指定值。
不能为具有 IDENTITY 属性的列指定值，除非该列的 IDENTITY_INSERT 为 ON。当 IDENTITY_INSERT 为 ON 时，INSERT 语句必须提供一个值。
INSERT 语句必须为所有无 DEFAULT 约束的 NOT NULL 列提供值。

对于除计算列、标识列或 timestamp 列以外的任何列，任何允许空值的列或具有 DEFAULT 定义的 NOT NULL 列的值都是可选的。
当 INSERT、UPDATE 或 DELETE 语句引用具有 INSTEAD OF 触发器的视图时，数据库引擎将调用该触发器，而不是对任何表采取任何直接操作。即使为视图生成的 inserted 和 deleted 表中的信息格式与基表中的数据格式不同，该触发器在生成执行基表中的请求操作所需的任何语句时，仍必须使用 inserted 和 deleted 表中的信息。

传递到在视图上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表格式与为该视图定义的 SELECT 语句的选择列表相匹配。例如：

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT EmployeeID, LastName, FirstName
FROM Northwind.dbo.Employees

视图的结果集有三列：一个 int 列和两个 nvarchar 列。传递到在视图上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表也具有名为 EmployeeID 的 int 列、名为 LName 的 nvarchar 列和名为 FName 的 nvarchar 列。

视图的选择列表还包含不直接映射到单个基表列的表达式。一些视图表达式（如常量调用或函数调用）可能不引用任何列，这类表达式会被忽略。复杂的表达式会引用多列，但在 inserted 和 deleted 表中，每个插入的行仅有一个值。如果视图中的简单表达式引用具有复杂表达式的计算列，则这些简单表达式也有同样的问题。视图上的 INSTEAD OF 触发器必须处理这些类型的表达式。有关更多信息，请参见视图上 INSTEAD OF 触发器中的表达式和计算列。

12.5 触发器概述
在上面几节我们介绍了一般意义的存储过程即用户自定义的存储过程和系统存储过
程本节将介绍一种特殊的存储过程即触发器在余下各节中我们将对触发器的概念
作用以及对其的使用方法作详尽介绍使读者了解如何定义触发器创建和使用各种不同
复杂程度的触发器
12.5.1 触发器的概念及作用
触发器是一种特殊类型的存储过程它不同于我们前面介绍过的存储过程触发器主
要是通过事件进行触发而被执行的而存储过程可以通过存储过程名字而被直接调用当
对某一表进行诸如UPDATE INSERT DELETE 这些操作时SQL Server 就会自动执行
触发器所定义的SQL 语句从而确保对数据的处理必须符合由这些SQL 语句所定义的规
则
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数
据的一致性除此之外触发器还有其它许多不同的功能
1 强化约束Enforce restriction
触发器能够实现比CHECK 语句更为复杂的约束
2 跟踪变化Auditing changes
触发器可以侦测数据库内的操作从而不允许数据库中未经许可的指定更新和变化
3 级联运行Cascaded operation
触发器可以侦测数据库内的操作并自动地级联影响整个数据库的各项内容例如
某个表上的触发器中包含有对另外一个表的数据操作如删除更新插入而该操作
又导致该表上触发器被触发
4 存储过程的调用Stored procedure invocation
为了响应数据库更新触发器可以调用一个或多个存储过程甚至可以通过外部过程
的调用而在DBMS 数据库管理系统本身之外进行操作
由此可见触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等
一些方面的问题例如触发器能够找出某一表在数据修改前后状态发生的差异并根据
这种差异执行一定的处理此外一个表的同一类型INSERT UPDATE DELETE 的
多个触发器能够对同一种数据操作采取多种不同的处理
总体而言触发器性能通常比较低当运行触发器时系统处理的大部分时间花费在
参照其它表的这一处理上因为这些表既不在内存中也不在数据库设备上而删除表和插
入表总是位于内存中可见触发器所参照的其它表的位置决定了操作要花费的时间长短
12.5.2 触发器的种类
SQL Server 2000 支持两种类型的触发器AFTER 触发器和INSTEAD OF 触发器其
中AFTER 触发器即为SQL Server 2000 版本以前所介绍的触发器该类型触发器要求只
有执行某一操作INSERT UPDATE DELETE 之后触发器才被触发且只能在表
上定义可以为针对表的同一操作定义多个触发器对于AFTER 触发器可以定义哪一
个触发器被最先触发哪一个被最后触发通常使用系统过程sp_settriggerorder 来完成此
任务
INSTEAD OF 触发器表示并不执行其所定义的操作INSERT UPDATE DELETE
而仅是执行触发器本身既可在表上定义INSTEAD OF 触发器也可以在视图上定义
INSTEAD OF 触发器但对同一操作只能定义一个INSTEAD OF 触发器

 

--替代操作触发器举例：

create table a (a int,aa varchar(100))
create table b (b int,bb varchar(100))
go
create view c
as
select a.*,b.* from a,b where a.a=b.b
go

--测试：
insert c values(1,'aa',1,'bb')
--失败

go
CREATE TRIGGER 名 on c
INSTEAD OF INSERT
AS
BEGIN
  INSERT a select a,aa from inserted
  INSERT b select b,bb from inserted
END
go

--测试：
insert c values(1,'aa',1,'bb')

 

 

instead of触发器一般用于视图的更新:

例子:
--表1
create table tb1(id int,name varchar(10))

--表2
create table tb2(id int,des varchar(10))

--视图
create view aa
as
select a.id,a.name,b.des from tb1 a inner join tb2 b on a.id=b.id

--要实现向视图aa中插入数据时,能自动插入数据到tb1和tb2中,则在视图aa中创建触发器
create trigger t_insert on aa
INSTEAD OF insert
as
insert into tb1 select id,name from inserted
insert into tb2 select id,des from inserted
go

--测试
insert into aa values(1,'aa','bb')

--显示插入结果
select * from tb1
select * from tb2