Event
Notifications
This is also a very beautiful feature of
SQL Server which was introduced with the release of SQL Server 2005. Event
Notifications mechanism is used for tracking all events or changes happening at
the database or instance level. Event Notifications convey the very same data
as DDL triggers and occur on the very same events, but they are asynchronous
and loosely coupled as SQL Trace.
Triggers
Triggers are extremely powerful and
useful feature of SQL because they are completely Global and Stealthy They are
good and necessary for data integrity as well as complete the DBMS-specific
activity under the control of the database where it belongs. They are basically
used for audit logging.
“Triggers are stored
programs, which are automatically executed or fired when some events occur on
the table like insertion, deletion or updation of data. It is a database object
which could be defined on the table, view, schema, or database with which the
event is associated”.
T-SQL vs PL/SQL is going to vary in importance depending on whether you write database software (scripts, stored procedures, functions, etc.), or if you are more purely administrative. 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 T-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_triggers catalog views.
|
Metadata about event
notifications is found in the sys.event_notifications and
sys.server_event_notifications catalog views.
|
No comments:
Post a Comment