Sql Server触发器的原理及应用技巧

 

1、触发器的 '本质'：

触发器是一种特殊的存储过程，它不能被显式地调用， 而是在往表中插入记录、更改记录或者删除记录时，当事件发生时，才被 自动地激活。

2、这样做带来的 '功能'：

触发器可以用来对表实施复杂的完整性约束，保持数据的一致性，当触发器所保护的数据发生改变时，触发器会自动被激活， 响应同时执行一定的操作（对其它相关表的操作），从而保证对数据的不完整性约束或不正确的修改。

触发器可以查询其它表，同时也可以执行复杂的T-SQL语句。触发器和引发触发器执行的命令被当作一次事务处理，因此就具备了事务的所有特征。

注意： '事务具备什么特征？在触发器中的作用？'如果发现引起触发器执行的T-SQL语句执行了一个非法操作，比如关于其它表的相关性操作，发现数据丢失或需调用的数据不存在，那么就回滚到该事件执行前的SQL SERVER数据库状态。

3、触发器的作用：

触发器可以对数据库进行级联修改，这一点刚才已经说过了。

需要说明的是： '触发器和约束的关系和区别'

(1)一般来说，使用约束比使用触发器效率更高。

(2)同时，触发器可以完成比CHECK约束更复杂的限制。

说明：

2.1 与CHECK约束不同，在触发器中可以引用其它的表。

2.2 触发器可以发现改变前后表中数据的不一致，并根据这些不同来进行相应的操作。

2.3 对于一个表不同的操作（INSERT、UPDATE、DELETE）可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器来完成不同的操作。

举例1：在签订一份订单时，货物的库存量应减少。

问？这应用了触发器的什么特征？CHECK约束能解决吗？

举例2：正在进行整理的货物不能下订单。

问？这应用了触发器的什么特征？CHECK约束能解决吗？

4、对触发器3种操作的分析：

在SQL SERVER为每个触发器都创建了两个专用表：inserted表和deleted表。

这是两个逻辑表，由系统来维护，在触发执行时存在，在触发结束时消失。

这样有什么用途？

带着问题看，具体操作步骤和过程：

(1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。

在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted表，这两个表不会有共同的行。

(2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。

在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中，inserted表的内容是激活触发器的表中新行的拷贝。

说明：update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。

11.1.3 instead of 和 after 触发器

主要包括定义和应用范围条件，操作执行时机；

11.2 创建触发器

1、考虑为什么要设计出发器，为解决什么问题而设计？

2、应制定的内容：为什么，大家思考一下？不知道，看下面的例子，全看完！

T-SQL语句创建触发器

语法结构：

以下是引用片段：
create trigger 触发器名
on 表或视图
for|after|instead of --操作时机
insert,update,delete
as
sql语句

作业：

(要求：在northwind表中建立2个表：cust_test和order_test)

以下是引用片段：
cust_test: CustomerID char(5) PK   order_test:
CustomerID char(5)--对应关系
Custcity                            Orderid     PK
Custname                            OrderNames
CStatus     int                      OStatus     int --状态
Cstorage    int                      Orders      int --定购量和库存量
Cdate       date                     Odate       date--日期

作业1：
在cust_test表中建立删除触发器，实现上述2表的级联删除。

作业2：
在order_test表建立insert触发器，当向order_test表插入一行，如果cust_test表中对应记录status值为1，说明处于准备状态不能写入该数据。

以下是引用片段：
答案1：
use northwind
go
create trigger cust_orders_del1
on Cust_test
after delete
as
delete from order_test
where CustomerID in
(select CustomerID from deleted)
go

答案2：
use northwind
go
create trigger cust_orders_ins2
on order_test
after insert
as
if (select cstatus from cust_test,inserted
where cust_test.customerid=inserted.customerid)=1
begin
print ’The Goods is being processed’
rollback transaction
end
go

图形化操作触发器

11.3 查看触发器情况

图形化操作结合T-SQL命令

(1)sp_helptrigger 触发器名
查看触发器的名称，拥有者和五个布尔值

以下是引用片段：
supdate,isdelete,isinsert,isafter,isinsteadof

(2)sp_helptext 触发器名
查看文本信息

(3)设置某一触发器的无效和重新有效

以下是引用片段：
无效：
use northwind
alter table 表名
disable trigger 触发器名

重新有效：
use northwind
alter table 表名
enable trigger 触发器名
(4）删除触发器

以下是引用片段：
use northwind
drop trigger 触发器名，触发器名

作业3：
在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。

作业4：
在order_test表上建立一个插入触发器，规定订单日期(Odate)不能手工修改。

作业5：
要求订购的物品一定要在仓库中有的，并且数量足够。

作业6：
在order_test表上建立一个插入触发器，同时插入多行数据时，要求订购的物品一定要在仓库中有的。

答案3：

以下是引用片段：
use northwind
go
create trigger cust_orders_ins3
on order_test
after insert
as
update cust_test
set cstorage=cstorage-inserted.orders
from cust_test,inserted
where cust_test.customerid=inserted.customerid

答案4：

以下是引用片段：
use northwind
go
create trigger orderdateupdate
on order_test
after update
as
if update (odate)
begin
raiserror(’Error’,10,1)
rollback transaction
end

答案5：

以下是引用片段：
use northwind
go
create trigger order_insert5
on order_test
after insert
as
begin
if(select count(*)
from cust_test,inserted
where cust_test.customerid=inserted.customerid)=0
begin
print ’No entry in goods for your order’
rollback transaction
end
if(select cust_test.cstorage from cust_test,inserted
where cust_test.customerid=inserted.customerid)<
(select inserted.orders from cust_test,inserted
where cust_test.customerid=inserted.customerid)
begin
print ’No enough entry in goods for your order’
rollback transaction
end
end

答案6：

以下是引用片段：
use northwind
go
create trigger order_insert6
on order_test
after insert
as
if
(select count(*) from cust_test,inserted
where cust_test.customerid=inserted.customerid)<>@@rowcount
--可以在触发器逻辑中使用 @@ROWCOUNT 函数以区分单行插入和多行插入。
begin
delete order_test from order_test,inserted
where order_test.orderid=inserted.orderid and
inserted.customerid not in (select customerid from cust_test)
end

print @@rowcount

Transact-SQL 参考
SET ROWCOUNT
使 Microsoft? SQL Server? 在返回指定的行数之后停止处理查询。
语法
以下是引用片段：
SET ROWCOUNT { number | @number_var }

参数
以下是引用片段：
number | @number_var

是在停止给定查询之前要处理的行数（整数）。
注释
建议将当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句重新编写为使用 TOP 语法。有关更多信息，请参见 DELETE、INSERT 或 UPDATE。
对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句，忽略 SET ROWCOUNT 选项设置。
若要关闭该选项（以便返回所有的行），请将 SET ROWCOUNT 指定为 0。
说 明 设置 SET ROWCOUNT 选项将使大多数 Transact-SQL 语句在已受指定数目的行影响后停止处理。这包括触发器和 INSERT、UPDATE 及 DELETE 等数据修改语句。ROWCOUNT 选项对动态游标无效，但限制键集的行集和不感知游标。使用该选项时应谨慎，它主要与 SELECT 语句一起使用。
如果行数的值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。
SET ROWCOUNT 的设置是在执行或运行时设置，而不是在分析时设置。
权限
SET ROWCOUNT 权限默认授予所有用户。
示例
SET ROWCOUNT 在指定的行数后停止处理。在下例中，注意有 x 行满足预付款少于或等于 $5,000 的条件；但是，从更新所返回的行数中可以看出并非所有的行都得到处理。ROWCOUNT 影响所有的 Transact-SQL 语句。
以下是引用片段：
USE pubs
GO
SELECT count(*) AS Cnt
FROM titles
WHERE advance >= 5000
GO

下面是结果集：
以下是引用片段：
Cnt       
-----------
11          

(1 row(s) affected)

现在，将 ROWCOUNT 设置为 4，并更新预付款等于或大于 $5,000 的所有行。
SET ROWCOUNT to 4.
SET ROWCOUNT 4
GO
UPDATE titles
SET advance = 5000
WHERE advance >= 5000
GO

 

===============================================

实例：

Create Trigger trg_Insert_recentRemarks
on sys_RecentRemarks
instead of insert
as
declare @hostname nvarchar(50)
declare @newremarks nvarchar(100)
declare @Minlasttime datetime
declare @bExist int --备注是否已存在
declare @count int

select @hostname = hostname from inserted
select @newremarks = remarks from inserted
select @count = Count(*) from sys_RecentRemarks where hostname = @hostname
select @Minlasttime = Min(lasttime) from sys_RecentRemarks where hostname = @hostname
select @bExist = Count(*) from sys_RecentRemarks where hostname = @hostname And remarks = @newremarks

if @bExist <> 0
begin
   update sys_RecentRemarks
    set lasttime = getdate()
   where remarks = @newremarks
end
else if @count = 30
begin
   update sys_RecentRemarks
    set lasttime = getdate(),
     remarks = @newremarks
   where lasttime = @Minlasttime  
end
else
begin
   insert into sys_RecentRemarks (hostname, remarks, lasttime)
   values(@hostname, @newremarks, getdate())
end
知识点有：

1）“instead of insert”表示该触发器将代替Insert操作，原先的insert操作将不在被执行。

2）select @hostname = hostname from inserted "inserted“是个伪表表示原先要插入的数据。

3）if else if else 其中的begin、 end和c++里左括号、右括号对应。

解释：

表sys_RecentRemarks包含的字段有hostname, remarks,lasttime.当执行插入操作时，

       如果表里已经有相同的hostname和remarks的行，则更新该行的日期为当前日期。

       否则 如果该hostname有的remarks已经有30条了，那么把最老的（lasttime最小）的remarks设置为要插入的remarks，并更新日期为当前。

       否则 则插入要插入的hostname和remarks并获得当前日期也插入。

该触发器的功能：

可以保证表里同一个hostname拥有的remarks不会多于30条。lasttime反映了该remarks最新的插入时间。