SQL Server Event Notifications Example

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events.

The major difference between the triggers and event notifications is that the triggers are fired in synchronous mode to execute a specific code within the same session and transaction. Whereas, the event notification do not execute any code, it only sends information in asynchronous mode which then can be logged and acted upon later on.


Here is a snippet from the Microsoft Documentation that explains the differences:

Event Notifications vs. Triggers

The following table compares and contrasts triggers and event notifications.

Triggers	Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.	Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run Transact-SQL or common language runtime (CLR) managed code.	Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.	Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.	The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.	Event notifications can be processed on a remote server.
Triggers can be rolled back.	Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.	Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.	The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.	Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.	Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggerscatalog views.	Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

---

In the following sample SQL script, I am creating an Extended Event to capture  ALTER TABLE events in a database.

-- Create a brand new database for the testing purpose
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go
CREATE DATABASE [temp_event_notification_test_929368242990-321836]
GO


-- Enable the service broker if its not already
if not exists 

(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_broker_enabled = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] SET ENABLE_BROKER; 
go

-- set the trustworth property ON
if not exists 
(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_trustworthy_on = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] 
SET TRUSTWORTHY ON;
go

-- check if there is already a service broker end point running
if not exists (select * from sys.service_broker_endpoints 
               where type_desc = 'SERVICE_BROKER' 
			     and state_desc = 'STARTED' 
			  )
BEGIN
    -- check if there is a SB endpoint with same name
    if not exists 
	(select * from sys.service_broker_endpoints 
	 where NAME = 'en_service_broker_929368242990-321836')
 
 BEGIN
    -- check to make sure the tcp port is not already in use
    if not exists (SELECT * FROM SYS.tcp_endpoints where port = 5122)
	   CREATE ENDPOINT [en_service_broker_929368242990-321836]
	   STATE = STARTED
	   AS TCP (  LISTENER_PORT = 5122)
	   FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS  );

    else
		raiserror(
		'Error: An end point cannot be created. 
		 Please check if there is already one with same port.', 16,1)
 END
 else
    raiserror(
	'Error: An end point cannot be created. 
	 Please check if there is already one with same name.', 16,1)
END
GO
USE [temp_event_notification_test_929368242990-321836]

go
CREATE QUEUE [ent_929368242990-321836] ;  
GO  
CREATE SERVICE [ens_929368242990-321836]  
ON QUEUE [ent_929368242990-321836]  
(  
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]  
);  
GO  

CREATE ROUTE [enr_929368242990-321836]  
WITH SERVICE_NAME = 'ens_929368242990-321836',  
ADDRESS = 'LOCAL';  
GO  

CREATE EVENT NOTIFICATION [enen_929368242990-321836]  
ON DATABASE  
FOR ALTER_TABLE  
TO SERVICE 'ens_929368242990-321836',  'current database'

-- Test
-- Generate the events
if object_id('entt_929368242990-321836') is not null
drop table [entt_929368242990-321836]
go
create table [entt_929368242990-321836] (i int)
go
alter table [entt_929368242990-321836] add b int
go

-- verify/display that the event notification was captured
SELECT TOP (1000) *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836] 

if @@ROWCOUNT = 0
   RAISERROR(
   'Error: Something is not right. Event notification was not captured.', 16,1)

else 
   SELECT 'Success!' Msg
go


-- clear the records from the queue
-- RECEIVE display the event as well as removes it from the queue

RECEIVE * FROM [ent_929368242990-321836]
go
-- verify that the queue is now empty

SELECT TOP (1000) *, casted_message_body = 
	CASE message_type_name WHEN 'X' 
	  THEN CAST(message_body AS NVARCHAR(MAX)) 
	  ELSE message_body 
	END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836]

/*
Since I am only testing, I am using the following code to clean up afterwards
*/

/* CLEAN UP
USE [temp_event_notification_test_929368242990-321836]
go
if exists 
(SELECT * FROM sys.event_notifications 
 where name = '[enen_929368242990-321836]' and parent_class_desc = 'DATABASE')

	DROP EVENT NOTIFICATION [enen_929368242990-321836]  ON DATABASE;  

go

if exists (select  * from sys.routes 
           where name = '[enr_929368242990-321836]' and address = 'LOCAL')

	DROP ROUTE [enr_929368242990-321836] 

if exists (SELECT * FROM sys.services 
           where name = 'ens_929368242990-321836')

	DROP SERVICE [ens_929368242990-321836]
GO
if exists (SELECT * FROM sys.service_queues 
           where name = 'ent_929368242990-321836' 
		     and schema_id = 1)

	DROP QUEUE [dbo].[ent_929368242990-321836]
GO
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go

*/

Download this script at the GitHub:

SQL Server Event Notifications Example