1.为数据库VIAOSDB 启用缓存依赖，自动生成以下数据表和存储过程
--表
AspNet_SqlCacheTablesForChangeNotification
--存储过程
AspNet_SqlCachePollingStoredProcedure
AspNet_SqlCacheQueryRegisteredTablesStoredProcedure
AspNet_SqlCacheRegisterTableStoredProcedure
AspNet_SqlCacheUnRegisterTableStoredProcedure
AspNet_SqlCacheUpdateChangeIdStoredProcedure
以下为启用语句
.aspnet_regsql.exe -S 10.14.7.215 -U sa -P Admin1234 -d VIAOSDB -ed ----SQLSERVER 验证
或 .aspnet_regsql.exe -S server -E -d VIAOSDB -ed ----WINDOWS验证
2.为缓存依赖启用某表，自动为表生成触发器
--触发器
AgentInfo_AspNet_SqlCacheNotification_Trigger
以下为启用语句
.aspnet_regsql.exe -S 10.14.7.215 -E -d VIAOSDB -t AgentInfo -et ----为缓存依赖启用该表 （AgentInfo）
.aspnet_regsql.exe -S . -E -d VIAOSDB -t InspectionItem -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t InspectionService -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t Permission -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t RoleToPermission -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t UserInfo -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t UserRole -et
.aspnet_regsql.exe -S . -E -d VIAOSDB -t VIStationInfo -et
3.基本原理：缓存依赖为需要的表设置了一个变化情况表AspNet_SqlCacheTablesForChangeNotification以及触发器，当启用缓存依赖的表发生变化时，
各表的触发器会将变化情况记录到表AspNet_SqlCacheTablesForChangeNotification，服务器端程序会根据词表变化情况，更新缓存。
-----------------------------------------------------------附表脚本、存储过程脚本、触发器脚本---------------------------------------
表脚本
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
此文参考：https://www.jb51.net/article/84431.htm
