SQL时间戳使用之缓存依赖

通过时间戳来实现缓存依赖的一种方式

1.创建时间戳存储表

USE [iwomKeyWord]
GO

/****** Object:  Table [dbo].[CachedTimemap]    Script Date: 12/01/2012 01:25:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CachedTimemap](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[tableName] [char](30) NOT NULL,
	[value] [int] NULL,	
	[timemap] [timestamp] NULL,
 CONSTRAINT [PK_CachedTimemap] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CachedTimemap]  WITH NOCHECK ADD  CONSTRAINT [CK_isExitTable] CHECK  ((object_id([tableName],'U') IS NOT NULL))
GO

ALTER TABLE [dbo].[CachedTimemap] CHECK CONSTRAINT [CK_isExitTable]
GO

ALTER TABLE [dbo].[CachedTimemap] ADD  CONSTRAINT [DF_CachedTimemap_value]  DEFAULT ((1)) FOR [value]
GO

2.创建一个标量函数来获得时间戳表的名称

USE [iwomKeyWord]
GO
/****** Object:  UserDefinedFunction [dbo].[GetTimemapName]    Script Date: 12/01/2012 01:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Arvin>
-- Create date: <2012-11-30>
-- Description:	<获得表名>
-- =============================================
ALTER FUNCTION [dbo].[GetTimemapName] 
(
	@TABLENAME CHAR(30)
)
RETURNS CHAR(30)
AS
BEGIN
	DECLARE @FLAG INT
	SET @FLAG=0
	SELECT @FLAG=1 WHERE @TABLENAME LIKE 'dbo.%'
	IF(@FLAG=1)
	SET @TABLENAME=Substring(@TABLENAME,5,25)
	RETURN @TABLENAME
END

3.创建存储过程：查看表数据是否存在更新（）

USE [iwomKeyWord]
GO
/****** Object:  StoredProcedure [dbo].[pr_IsUpdateData]    Script Date: 12/01/2012 01:42:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<arvin>
-- Create date: <2012-11-30>
-- Description:	<判断一个表的数据是否更新>
-- =============================================
ALTER PROCEDURE [dbo].[pr_IsUpdateData]
@tableName char(30),
@beforeTimemap timestamp,
@timemapValue timestamp out	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE	@currentTimemap timestamp
	DECLARE @COUNT INT
	SET @COUNT=0
	SET @TABLENAME=dbo.GetTimemapName(@TABLENAME)	
	SELECT @COUNT=1 FROM dbo.CachedTimemap WHERE [tableName]=@TABLENAME		
	IF(@COUNT=0)
		BEGIN
			SET @timemapValue=0xFFFFFFFFFFFFFFFF
			RETURN
		END		
	SELECT @currentTimemap=[timemap] FROM  dbo.CachedTimemap WHERE  [tableName]=@TABLENAME		
	IF(@currentTimemap=ISNULL(@beforeTimemap,0))
		SET @timemapValue=0
	ELSE
		SET @timemapValue=@currentTimemap
END

4.创建存储过程：更新时间戳

USE [iwomKeyWord]
GO
/****** Object:  StoredProcedure [dbo].[pr_UpdateCachedTimemap]    Script Date: 12/01/2012 01:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<arvin>
-- Create date: <2012-11-30>
-- Description:	<更新时间戳表>
-- =============================================
ALTER PROCEDURE [dbo].[pr_UpdateCachedTimemap]
@tableName char(30)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE	@beforeTimemap timestamp
	DECLARE @COUNT INT
	SET		@COUNT=0
	SET @TABLENAME=dbo.GetTimemapName(@TABLENAME)
	SELECT @COUNT=1 FROM dbo.CachedTimemap WHERE [tableName]=@TABLENAME
	IF(@COUNT=0)
		INSERT INTO dbo.CachedTimemap ([tableName])VALUES(@TABLENAME)
	UPDATE [dbo].[CachedTimemap]
		SET [value] = 1
	WHERE [tableName]=@tableName
END

5.在需要实现缓存依赖的表创建触发器，与时间戳更新存储过程关联

USE [iwomKeyWord]
GO
/****** Object:  Trigger [dbo].[TR_updateCachedTimemap]    Script Date: 12/01/2012 01:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[TR_updateCachedTimemap] 
   ON   [dbo].[WarnKeyWord]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE	@return_value int
	EXEC	@return_value = [dbo].[pr_UpdateCachedTimemap]
			@tableName = N'WarnKeyWord'
END

#########################使用方法

通过调用存储过程（pr_IsUpdateData），来判断是否更新替换本地内存中的数据。