T-SQL Script: CREATE EVENT NOTIFICATION

^{Current .Net Version: 2.0.50727}

Mike's C#, VS.NET and SQL Blog

T-SQL Scripts

CREATE EVENT NOTIFICATION

-- Tested with SQL Server 2005 9.00.852
-- Creates an event notification for a database 
-- and sends a message to a Service Broker queue 
-- whenever a table is created in that database.
-- This is a Service Broker feature.
USE DotNetFun;
BEGIN TRY
	BEGIN TRAN
	-- If there isn't already a queue to hold 
	-- Event Notifications, create one:
	CREATE QUEUE DotNetFunEventNotificationQueue
		WITH STATUS = ON, 
		RETENTION = OFF;
	-- Bind a service to the preceding queue; we'll 
	-- use this service for our event notifications:
	CREATE SERVICE DotNetFunEventNotificationService 
		ON QUEUE DotNetFunEventNotificationQueue 
		([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
	-- NOTE: The message type...
	-- http://schemas.microsoft.com/SQL/Notifications/PostEventNotification
	-- ... is already present and available for event 
	-- notifications.
	-- Now, create an event notifications that sends event 
	-- messages to the DotNetFunEventNotificationQueue 
	-- queue whenever a table is created in the 
	-- DotNetFun database:
	CREATE EVENT NOTIFICATION notify_CREATE_TABLE 
		ON DATABASE 
		FOR CREATE_TABLE 
		TO SERVICE DotNetFunEventNotificationService;
	-- This statement will trigger the notify_CREATE_TABLE 
	-- event:
	CREATE TABLE TEMPTABLEXYZ (ID INT IDENTITY PRIMARY KEY);
	COMMIT TRAN
END TRY
BEGIN CATCH
	-- Rollback all statements if any of them failed.
	ROLLBACK TRAN
END CATCH

Open/Save | E-mail | Back