触发器

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 表或视图
forafterinstead of --操作时机
insert,update,delete
as
sql语句

作业:

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

cust_test: CustomerID char(5) PK  order_test:
CustomerID char(5)--对应关系
Custcity
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 表或视图
forafterinstead of --操作时机
insert,update,delete
as
sql语句

作业:

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

cust_test: CustomerID char(5) PK  order_test:
CustomerID char(5)--对应关系
Custcity
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
问:请问怎样才能解决ms serer 2000 最大流水号的问题?


答:我可以介绍两种方法给你:


方法1:使用SCOPE_IDENTITY取得刚刚插入的最大流水号


/*建立traddeinfo表各相关索引,注意Trade_id字段必段是identity型*/

create table Tradeinfo(Trade_id int identity(1,1),Tdtime datetime Tdname varchar(20))

create index idx_trade_id on tradeinfo(trade_id)

/*使用scope_identity()中断函数,取得当前脚本范围内最后insert进去的identity值*/

declare @mytradeid int

set @mytradeid=0

begin transaction

   insert into tradeinfo values(getdata(),'test')

  select @mytradeid = Scope_identite()

commit transaction

/*把@mytradeid 变量中存放的流水号显示出来*/

select @mytradeid

方案2:结合uniqueidentifier和identity取得最大流水号


/*创建辅助表 id字段是identity类型,global_id是gudi类型*/

cretate table max_tradeid(id int identity(1,1),global_id uniqueidenfier)

/*创建索引以加速后面的select 语句*/

create index idx_guid on max_trade_id(global_id)

declare @mytradeid int.,@myglobal_id uniqueidentifier

set @mytradeid = e-1

/*利用newid ()函数取得一个全局叭一guid .由于guid的特性,不用别的并发进程会产生同样的guid值 */

jselect @myglobal_id = newid()

insert into max_trade_id values(@myglobl_id)

/*使用全局唯一guid得到刚刚插入的id值*/

select @mytradeid = id from max_trade_id  where globl_id =@myglobal _id

selecgt @mytradeid

注:这种方法的好处是:它的并发性好,不会引起阻在塞,而且保证了insert语句肯定执行成功,这种方法的缺点是多一个guid。

posted @ 2009-06-11 09:20  xumingming  阅读(485)  评论(0编辑  收藏  举报