SQL Server缓存依赖设置

USE [VIAOSDB]
GO

/****** Object:  Table [dbo].[AspNet_SqlCacheTablesForChangeNotification]    Script Date: 2020/7/3 9:38:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification](
	[tableName] [NVARCHAR](450) NOT NULL,
	[notificationCreated] [DATETIME] NOT NULL DEFAULT (GETDATE()),
	[changeId] [INT] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED 
(
	[tableName] 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

USE [VIAOSDB]
GO

/****** Object:  StoredProcedure [dbo].[AspNet_SqlCachePollingStoredProcedure]    Script Date: 2020/7/3 9:38:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] AS
         SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification
         RETURN 0
GO

/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure]    Script Date: 2020/7/3 9:38:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] 
         AS
         SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification   
GO

/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]    Script Date: 2020/7/3 9:38:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS
         BEGIN

         DECLARE @triggerName AS NVARCHAR(3000) 
         DECLARE @fullTriggerName AS NVARCHAR(3000)
         DECLARE @canonTableName NVARCHAR(3000) 
         DECLARE @quotedTableName NVARCHAR(3000) 

         /* Create the trigger name */ 
         SET @triggerName = REPLACE(@tableName, ‘[‘, ‘__o__‘) 
         SET @triggerName = REPLACE(@triggerName, ‘]‘, ‘__c__‘) 
         SET @triggerName = @triggerName + ‘_AspNet_SqlCacheNotification_Trigger‘ 
         SET @fullTriggerName = ‘dbo.[‘ + @triggerName + ‘]‘ 

         /* Create the cannonicalized table name for trigger creation */ 
         /* Do not touch it if the name contains other delimiters */ 
         IF (CHARINDEX(‘.‘, @tableName) <> 0 OR 
             CHARINDEX(‘[‘, @tableName) <> 0 OR 
             CHARINDEX(‘]‘, @tableName) <> 0) 
             SET @canonTableName = @tableName 
         ELSE 
             SET @canonTableName = ‘[‘ + @tableName + ‘]‘ 

         /* First make sure the table exists */ 
         IF (SELECT OBJECT_ID(@tableName, ‘U‘)) IS NULL 
         BEGIN 
             RAISERROR (‘00000001‘, 16, 1) 
             RETURN 
         END 

         BEGIN TRAN
         /* Insert the value into the notification table */ 
         IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName) 
             IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName) 
                 INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
                 VALUES (@tableName, GETDATE(), 0)

         /* Create the trigger */ 
         SET @quotedTableName = QUOTENAME(@tableName, ‘‘‘‘) 
         IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ‘TR‘) 
             IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ‘TR‘) 
                 EXEC(‘CREATE TRIGGER ‘ + @fullTriggerName + ‘ ON ‘ + @canonTableName +‘
                       FOR INSERT, UPDATE, DELETE AS BEGIN
                       SET NOCOUNT ON
                       EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N‘ + @quotedTableName + ‘
                       END
                       ‘)
         COMMIT TRAN
         END
   
GO

/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure]    Script Date: 2020/7/3 9:38:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS
         BEGIN

         BEGIN TRAN
         DECLARE @triggerName AS NVARCHAR(3000) 
         DECLARE @fullTriggerName AS NVARCHAR(3000)
         SET @triggerName = REPLACE(@tableName, ‘[‘, ‘__o__‘) 
         SET @triggerName = REPLACE(@triggerName, ‘]‘, ‘__c__‘) 
         SET @triggerName = @triggerName + ‘_AspNet_SqlCacheNotification_Trigger‘ 
         SET @fullTriggerName = ‘dbo.[‘ + @triggerName + ‘]‘ 

         /* Remove the table-row from the notification table */ 
         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ‘AspNet_SqlCacheTablesForChangeNotification‘ AND type = ‘U‘) 
             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ‘AspNet_SqlCacheTablesForChangeNotification‘ AND type = ‘U‘) 
             DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHERE tableName = @tableName 

         /* Remove the trigger */ 
         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ‘TR‘) 
             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ‘TR‘) 
             EXEC(‘DROP TRIGGER ‘ + @fullTriggerName) 

         COMMIT TRAN
         END
   
GO

/****** Object:  StoredProcedure [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure]    Script Date: 2020/7/3 9:38:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] 
             @tableName NVARCHAR(450) 
         AS

         BEGIN 
             UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) SET changeId = changeId + 1 
             WHERE tableName = @tableName
         END
   
GO

USE [VIAOSDB]
GO
/****** Object:  Trigger [dbo].[AgentInfo_AspNet_SqlCacheNotification_Trigger]    Script Date: 2020/7/3 9:37:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AgentInfo_AspNet_SqlCacheNotification_Trigger] ON [dbo].[AgentInfo]
                       FOR INSERT, UPDATE, DELETE AS BEGIN
                       SET NOCOUNT ON
                       EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N‘AgentInfo‘
                       END