Q

What are Event Notifications?

SQL Server 2005 expert Adam Machanic discusses the similarities between Event Notifications and DDL triggers.

What are Event Notifications?
Event Notifications is a feature similar to DDL triggers but with slightly different capabilities. The most important difference is that Event Notifications uses SQL Service Broker to asynchronously deliver messages, rather than working in the scope of the transaction, as do DDL triggers. This means that whether or not the transaction completes, the Event Notification will still be delivered. Likewise, it means that Event Notifications have no control over the transaction, and cannot be used to restrict operations—no rollback is possible within a notification.

Like DDL triggers, Event Notifications can fire on database or server-level DDL operations. However, Event Notifications also has the capability of firing on trace events. For instance, an Event Notification can be created that will fire any time a deadlock event occurs. This capability offers a lot of flexibility for capturing server events, which was only possible via traces previously.

Also similar to DDL triggers, Event Notifications uses the EVENTDATA function for programmatic access to information about the event that fired the notification.

Event Notifications can be used instead of DDL triggers whenever asynchronous activities make more sense than transaction control. For instance, if you need to log a certain event, but do not need the ability to roll back the transaction if something isn't correct, Event Notifications are probably a better choice.


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in December 2005

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close