CREATE PROC procR

AS

IF @@TRANCOUNT <> 0 --启动新事务前先回滚旧事务

ROLLBACK TRAN

DECLARE @err int

BEGIN TRAN

UPDATE customers SET city = 'Dallas'

SELECT 1/0 --设置一个错误

SET @err = @@ERROR

IF @err <> 0

BEGIN

ROLLBACK TRAN

RETURN (@err)

END

COMMIT TRAN

GO

DECLARE @res int

EXEC @res = procR

SELECT @res

注：通过@@TRANCOUNT<>0可知有活动事务，执行ROLLBACK回滚当前活动连接的所有事务。当SQL Server使用连接池时（对WEB服务器而言相当常见），

在实际应用中编写此种逻辑就非常重要。由于一个虚连接可以留下一个打开的事务，该事务会影响使用同一物理连接的后续用户，因此，通过主动地

执行Restorer模式，让代码知道如何保护自己免受“无赖”事务和其他意外残余的影响。
 

重要提示

T－SQL的错误处理结构也并非无懈可击，它经常不是按预期的方式或它应该的方法运行。例如，存在许多严重的足以中断当前命令批处理的错误，当这些错误出现时，它让那些可能紧跟在其后的错误处理代码根本没有机会去执行。因此，当出现问题时，即使使用@@ERROR执行代码检查并调用ROLLBACK，还会有错误禁止ROLLBACK执行。这可能是导致孤立事务存在的根本原因，而且也是在开始一个事务前应检查孤立事务的原因。　

七、PROTOTYPE(原型)

此模式的目标：使用一种原型实例指定要创建对象的类型，并且通过复制原型创建新的对象。

1、最常见的实现方式是SELECT...INTO结构

SELECT *

INTO newCustomers

FROM Customers

SELECT *

INTO newCustomers

FROM Customers

WHERE country='UK'

注：通过指定一个列列表、WHERE子句、GROUP BY或HAVING子句，可在传送过程中修改原型。

2、复制表结构(T-SQL惯例中也曾提过)

SELECT *

INTO newCustomers

FROM Customers

WHERE　1 = 2

或

SELECT　TOP 0 *

INTO newCustomers

FROM Customers

注：通过错误的WHERE条件或不存在的行实现了复制表结构的功能

3、复制表时指定新数据

SELECT IDENTITY(int,1,1) AS CustNo,*

INTO newCustomers

FROM Customers

注：还可指定新列、通过联接选取来自其他表或视图的列、约束或函数等许多的可能性。

八、Singleton(单例）

此模式目标：确保在任何给定时间只存在一个类实例并且提供访问该实例的路径。

严格说，在关系数据库中，对于面向对象类的等价物是表。类的一个实例就是表中的一行。因此，Singleton模式的最显而意见的实现就是确保表中只包含一行。

CREATE TABLE LastCustNo

(LastCustNo int)

GO

INSERT LastCustNo VALUES(1)

GO

CREATE TRIGGER LastCustNoInsert ON LastCustNo

FOR INSERT

AS

IF (SELECT COUNT(*) FROM LastCustNo) > 1

BEGIN

RAISERROR('You May Not insert more than one row into this table!',16,10)

ROLLBACK TRAN

END

GO

INSERT LastCustNo VALUES(2) --由于触发器的原因，插入失败

GO

SELECT * FROM LastCustNo

注：由于触发器的原因，在任何时刻只允许在表中插入一行，如果表中已包含至少一行，在试图插入新的一行时将导致错误并回滚事务。

(1)IF (SELECT COUNT(*) FROM LastCustNo)必须用 > 1 ，而不能用＝1，因为除Instead Of触发器外，T－SQL触发器只在操作已完成，但还没提交给数据库前运行,这表明从触发器角度看，在事务回滚前，LastCustNo表看起来总是包含两行。

(2)禁止使用IF EXISTS(SELECT COUNT(*) FROM LastCustNo)测试表中的行，因对于触发器来说，新插入的行直到事务被回滚才出现在表中，因此，即使在插入前表为空，也将禁止向表中插入行。

实际应用：禁止一个应用的多个实例连接至服务器

方法1：应用程序锁

--锁定应用程序资源

DECLARE @res int

BEGIN TRAN

EXEC @res = sp_getapplock @Resource = 'Check Writer',@LockMode = 'Exclusive'

--返回到应用程序

--当检测到应用程序时执行以下代码(释放锁资源)

EXEC @res = sp_releaseapplock @Resource = 'Check Writer'

ROLLBACK TRAN

注：可在启动应用程序时启用一个锁，在关闭时释放该锁。通过以独占方式启用锁，可在释放该锁前禁止运行应用程序的另一个实例。

但这种方法让一个事务长期保持为打开状态。一般来说，不应该长时间或当一个用户被提示输入时让一个事务保持打开状态。

方法2：使用SET CONTEXT_INFO(推荐方法)

IF EXISTS (SELECT * FROM master..sysprocesses WHERE context_info = 0x123456)

RAISERROR('You Can run only one copy of this application at a time',20,1) WITH LOG

ELSE

SET CONTEXT_INFO 0x123456

注：使用SET CINTEXT_INFO命令在启动时间向sysprocesses插入一个用户自定义值，每次启动程序时检查该值，如果存在，则包含特定的记号连接已存在，因此产生一个错误并中止自己的连接。如不存在，则将该值保存在sysprocesses中，并继续加载应用。

九、FACADE（外观）

此模式目标：它给位于子系统的一个接口集合提供统一的接口。

在T-SQL中与此模式类似的是包含INSTEAD OF触发器的视图(INSTEAD OF触发器接受对视图的更新，并将它们分配给适当的底层表)。

CREATE TABLE AussieArtists

(ArtistID int identity,

LastName varchar(30),

FirstName varchar(30))

GO

INSERT AussieArtists VALUES('Gibb','Barry')

INSERT AussieArtists VALUES('Crowe','Russell')

INSERT AussieArtists VALUES('Hogan','Paul')

GO

CREATE VIEW VAussieArtists

AS

SELECT FirstName + '' + LastName AS Name FROM AussieArtists

GO

CREATE TRIGGER VAussieArtists_Insert ON VAussieArtists INSTEAD OF INSERT

AS

INSERT AussieArtists(FirstName,LastName)

SELECT LEFT(Name,ISNULL(NULLIF(CHARINDEX(' ',NAME),0),255)-1),

SUBSTRING(Name,NULLIF(CHARINDEX(' ',Name),0)+1,255)

FROM inserted

GO

INSERT VAussieArtists(Name) VALUES('Gerg Ham')

GO

SELECT * FROM AussieArtists

GO

DROP TABLE AussieArtists

DROP VIEW VAussieArtists

GO

注：因想在加入到基表前处理数据，所以使用INSTEAD OF触发器分析输入并执行插入数据，即对视图的简单插入被转换为对基表稍微复杂一些的插入。

十、Chain Of Responsibility(职责链)

此模式目标：为避免通过多个对象提供机会处理请求，合并请求的发送者与接收者。为实现该模式，必须串联接收对象并沿此链传送请求，直到某个对象处理它。

前面的Conveyor模式已接到责任链模式，这里再作深入讨论。在T－SQL中最接近此模式所描述行为的是嵌套触发器(触发器的执行导致其他触发器激活并实现串联行为的操作)。

CREATE TABLE employee (id int identity ,name varchar(10))

GO

CREATE TABLE laborage (id int ,salary int)

GO

CREATE TABLE laborage2(id int,number int)

GO

INSERT employee (name) values('zs')

INSERT laborage values(101,101)

INSERT laborage2 values(101,102)

GO

CREATE TRIGGER up_employee ON Employee

FOR UPDATE

AS

UPDATE laborage Set Salary = Salary + 100 WHERE id =101

GO

CREATE TRIGGER up_laborage ON laborage

FOR UPDATE

AS

UPDATE laborage2 SET number = number + 200 WHERE id = 101

GO

UPDATE Employee SET Name = 'Zxm' WHERE id = 1

GO

注：SP_CONFIGURE 'NESTED TRIGGER',0 可以禁止触发器嵌套,同时触发器最多嵌套32次。从功能角度考虑，在触发器未设定激活顺序情况下，插入请求从一个触发器传送给另一个。在任何情况下，如果其中的任一触发器拒绝插入并回滚事务，则整个操作都将被取消。

十一、COMMAND(命令)

这种模式目标：将请求一个对象来封装，允许你参数化包含不同请求、队列或日志请求的客户端，并支持可撤消操作。在T－SQL中与此模式对应的是事务。

CREATE PROC prClearLS

@intLsID int

AS

BEGIN TRAN

UPDATE Inventory SET Lease = 0 WHERE LsID = @intLsID

IF @@ERROR <> 0

GOTO PROBLEM

UPDATE LeaseSchedule

SET PeriodTotalAmount = 0

WHERE ScheduleID = @intLsID

IF @@ERROR <> 0

GOTO PROBLEM

COMMIT TRAN

RETURN 0

PROBLEM:

PRINT 'Unable to eliminate lease amounts from the database.'

ROLLBACK TRAN

RETURN 1