专注于互联网--专注于架构

 最新标签
网站地图
文章索引
Rss订阅

首页 »数据库 » 轻松掌握设计Sql Server触发器的原理 »正文

轻松掌握设计Sql Server触发器的原理

来源: 发布时间:星期二, 2009年2月3日 浏览:7次 评论:0
="t18">触发器


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 OStatus --状态
Cstorage Orders --定购量和库存量
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
(select cstatus from cust_test,inserted where

cust_test.customerid=inserted.customerid)=1
begin
pr 'The Goods is being processed'
rollback transaction
end
go




图形化操作触发器


11.3 查看触发器情况


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


(1)sp_helptrigger 触发器名


查看触发器名称拥有者和 5个布尔值


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 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
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
(select count(*)
from cust_test,inserted
where cust_test.customerid=inserted.customerid)=0
begin
pr 'No entry in goods for your order'
rollback transaction
end
(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
pr '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

(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

pr @@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

标签：

相关文章

读者评论

发表评论

  • 昵称：
  • 内容：

热门标签

精华推荐

最新标签

Dig排行

阅读排行

最新文章