Notificari de Evenimente

 

O alternativa la folosirea trigger-ilor DDL o constituie notificarile de evenimente. Acestea transmit informatiile legate de instructiuni DDL sau evenimente tip SQL Trace unui serviciu Service Broker.  Folosind Service Broker informatiile pot fi procesate asincron.

Crearea notificarilor de evenimente presupune doua etape:

• Crearea unui serviciu destinatie pentru receptionarea notificarii (si implicit a unei cozi si a unei rute)      
• Crearea notificarii de evenimente

 

Exemplu:

-- Etapa I

 

-- crearea unei cozi

CREATE QUEUE [dbo].[Coada-Notificare]

WITH STATUS = ON , RETENTION = OFF

GO

 

-- crearea serviciului

CREATE SERVICE [//Adventure-Works.com/Serviciu-Notificare]

AUTHORIZATION [dbo]

ON QUEUE [dbo].[Coada-Notificare] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

 

-- crearea rutei

CREATE ROUTE [Ruta-Notificare]

AUTHORIZATION [dbo]

WITH SERVICE_NAME = N'//Adventure-Works.com/Serviciu-Notificare', ADDRESS = N'LOCAL';

GO

 

 

-- Etapa II

 

-- notificare pentru evenimente LOGIN, LOGOUT

CREATE EVENT NOTIFICATION NotificariEvenimente_Login_Logout

ON SERVER

FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED

TO SERVICE '//Adventure-Works.com/Serviciu-Notificare', 'current database';

GO

 

-- receptionare eveniment

WAITFOR (

    RECEIVE

        CASE

            WHEN validation = 'X' THEN CAST(message_body as XML)

            ELSE NULL

        END AS message_body

        ,*

    FROM [AdventureWorks].[dbo].[Coada-Notificare]

), TIMEOUT 10000

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro