Event notifications in SQL Server are a powerful feature that allows you to respond to specific events, such as DDL statements and SQL Trace events. While they may seem similar to triggers, there are some key differences that make event notifications a unique tool in your SQL Server toolbox.
One major difference between triggers and event notifications is that triggers are fired synchronously within the same session and transaction, allowing you to execute specific code. On the other hand, event notifications do not execute any code. Instead, they send information in an asynchronous mode, which can be logged and acted upon later.
Let’s take a closer look at the differences between triggers and event notifications:
| Triggers | Event Notifications |
|---|---|
| DML triggers respond to data manipulation language (DML) 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. |
| 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_triggers catalog views. | Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views. |
Now that we understand the differences, let’s take a look at an example of how to create an event notification in SQL Server:
USE master;
GO
-- Create a brand new database for testing purposes
IF DB_ID('temp_event_notification_test') IS NOT NULL
DROP DATABASE [temp_event_notification_test];
GO
CREATE DATABASE [temp_event_notification_test];
GO
-- Enable the Service Broker if it's not already enabled
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'temp_event_notification_test' AND is_broker_enabled = 1)
ALTER DATABASE [temp_event_notification_test] SET ENABLE_BROKER;
GO
-- Set the trustworthy property ON
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'temp_event_notification_test' AND is_trustworthy_on = 1)
ALTER DATABASE [temp_event_notification_test] SET TRUSTWORTHY ON;
GO
-- Check if there is already a Service Broker endpoint running
IF NOT EXISTS (SELECT * FROM sys.service_broker_endpoints WHERE type_desc = 'SERVICE_BROKER' AND state_desc = 'STARTED')
BEGIN
-- Check if there is an SB endpoint with the same name
IF NOT EXISTS (SELECT * FROM sys.service_broker_endpoints WHERE NAME = 'en_service_broker')
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]
STATE = STARTED
AS TCP (LISTENER_PORT = 5122)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);
ELSE
RAISERROR('Error: An endpoint cannot be created. Please check if there is already one with the same port.', 16, 1);
END
ELSE
RAISERROR('Error: An endpoint cannot be created. Please check if there is already one with the same name.', 16, 1);
END
GO
USE [temp_event_notification_test];
GO
CREATE QUEUE [ent_queue];
GO
CREATE SERVICE [ens_service]
ON QUEUE [ent_queue]
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE ROUTE [enr_route]
WITH SERVICE_NAME = 'ens_service',
ADDRESS = 'LOCAL';
GO
CREATE EVENT NOTIFICATION [enen_notification]
ON DATABASE
FOR ALTER_TABLE
TO SERVICE 'ens_service', 'current database';
GO
-- Test
-- Generate the events
IF OBJECT_ID('ent_table') IS NOT NULL
DROP TABLE [ent_table];
GO
CREATE TABLE [ent_table] (i INT);
GO
ALTER TABLE [ent_table] 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].[dbo].[ent_queue] WITH (NOLOCK);
IF @@ROWCOUNT = 0
RAISERROR('Error: Something is not right. The event notification was not captured.', 16, 1);
ELSE
SELECT 'Success!' AS Msg;
GO
-- Clear the records from the queue
-- RECEIVE displays the event as well as removes it from the queue
RECEIVE * FROM [ent_queue];
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].[dbo].[ent_queue] WITH (NOLOCK);
Remember to clean up after testing by uncommenting the cleanup code at the end of the script.
Event notifications are a powerful tool in SQL Server that allow you to respond to specific events in an asynchronous manner. By understanding the differences between triggers and event notifications, you can leverage this feature to enhance your SQL Server applications.