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.
Dig Deeper on Microsoft SQL Server 2005
Related Q&A from Adam Machanic
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ... Continue Reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ... Continue Reading
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.