SQL时间戳使用之缓存依赖

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

1.创建时间戳存储表

USE [iwomKeyWord]GO/****** Object:  Table [dbo].[CachedTimemap]    Script Date: 12/01/2012 01:25:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[CachedTimemap]  WITH NOCHECK ADD  CONSTRAINT [CK_isExitTable] CHECK  ((object_id([tableName],'U') IS NOT NULL))GOALTER TABLE [dbo].[CachedTimemap] CHECK CONSTRAINT [CK_isExitTable]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Arvin>-- Create date: <2012-11-30>-- Description:<获得表名>-- =============================================ALTER FUNCTION [dbo].[GetTimemapName] (@TABLENAME CHAR(30))RETURNS CHAR(30)ASBEGINDECLARE @FLAG INTSET @FLAG=0SELECT @FLAG=1 WHERE @TABLENAME LIKE 'dbo.%'IF(@FLAG=1)SET @TABLENAME=Substring(@TABLENAME,5,25)RETURN @TABLENAMEEND

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

USE [iwomKeyWord]GO/****** Object:  StoredProcedure [dbo].[pr_IsUpdateData]    Script Date: 12/01/2012 01:42:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<arvin>-- Create date: <2012-11-30>-- Description:<判断一个表的数据是否更新>-- =============================================ALTER PROCEDURE [dbo].[pr_IsUpdateData]@tableName char(30),@beforeTimemap timestamp,@timemapValue timestamp outASBEGINSET NOCOUNT ON;DECLARE@currentTimemap timestampDECLARE @COUNT INTSET @COUNT=0SET @TABLENAME=dbo.GetTimemapName(@TABLENAME)SELECT @COUNT=1 FROM dbo.CachedTimemap WHERE [tableName]=@TABLENAMEIF(@COUNT=0)BEGINSET @timemapValue=0xFFFFFFFFFFFFFFFFRETURNENDSELECT @currentTimemap=[timemap] FROM  dbo.CachedTimemap WHERE  [tableName]=@TABLENAMEIF(@currentTimemap=ISNULL(@beforeTimemap,0))SET @timemapValue=0ELSESET @timemapValue=@currentTimemapEND

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

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

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

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

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

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