Enabling notification for individual tables

Q

Enabling notification for individual tables

✍: Guest

A

Once the necessary stored procedure and tables are created then we have to notify saying which table needs to be enabled for notifications.
That can be achieved by two ways:-
ã aspnet_regsql -et -E -d Northwind -t Products
ã Exec spNet_SqlCacheRegisterTableStoredProcedure 'TableName'
Registering tables for notification internally creates triggerfor the tables. For instance for a “products” table the following trigger is created. So any modifications done to the “Products” table will update the “AspNet_SqlCacheNotification’ table.

CREATE TRIGGER
dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON
[Products]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure
N'Products‘
END

“AspNet_SqlCacheTablesForChangeNotification” contains a single record for every table you're monitoring. When you make a change in the table (such as inserting, deleting or updating a record), the change Id column is incremented by 1.ASP.NET queries this table repeatedly keeps track of the most recent changed values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

2007-10-23, 4327👍, 0💬