How to make a Trigger when all you have are Events and Notifications
As usual, some of the best questions come from the newsgroups. And yet again, there was one that piqued by attention. The question essentially boiled down to “can you put a trigger on a login?” That is, the questioner wanted to execute some arbitrary T-SQL code whenever a user logged into a given instance of SQL Server. I’m not sure how you’d go about doing this in SQL Server 2000, but in 2005, two features make this possible – if you don’t mind building a bit of your own plumbing. These features are EVENT NOTIFICATIONS and the Service Broker.
Event Notifications are essentially fixtures that your create – using T-SQL – that allows SQL Server to dispatch messages about system events like logins, data definition language statements, and some trace events to Service Broker Services and Queues. The Service Broker Service acts as an address for the messages, while the Queue holds the messages until some a process like a Stored Procedure process them. Some number of instances of that Stored Procedure will be spawned by a process called Activation when a message arrives in a Queue. So these two features work together to deliver information about system events to a stored procedure that gets executed in response to that event, much like how a trigger gets fired in response to events.
Let’s see how you might wire this up. First, we need to be a bit of housekeeping, set up a sample database, enable Service Broker on that database and create a master key so that Service Broker can successfully decrypt messages:
use master
drop event notification ServerEventNotification on server
go
drop database en_test
create database en_test
use en_test
go
create master key encryption by password='jackthecat'
alter database en_test set new_broker
go
For our purpose, our virtual trigger will simply dump the information about the event into a simple table created with this T-SQL statement:
create table dbo.CaughtEvents(recorded dateTime default getdate(),eventdata xml);
Note that we’re not likely to need to join this information to other tables, so for now we won’t bother putting a primary key on this table. Nor will we do something else we normally would – put a primary XML index on the EVENTDATA column. In this case, we want the best insert performance we can get on this table and having to maintain the XML index in addition would incur more overhead than desired in normal usage. Of course if you’re going to make numerous queries over this data, the primary XML index would probably help performance of those queries considerable.
Our next step is code up that procedure which processes our event notifications. That’s not especially hard or complex, but it is worth breaking down a bit. There are essentially six parts such Stored Procedures should have. They are:
- The procedure signature
- Declarations of settings and needed local variables
- Poll the queue for available messages
- If any messages are available for processing, get the content of interest from them
- Do any processing on the message content needed and dispatch additional messages as needed
- Manage errors and transactions as needed
Part 1 – The procedure signature
Stored Procedures that get activated are commonly should have a very simple signature, simply the schema name followed by the name you want to give the procedure. There should not be any formal parameters to the procedure as none will be passed into by the activation process anyway – all of the procedures inputs, if you will, come from the queue and the messages therein. Our declaration looks like this:
create procedure dbo.ProcessEvent as begin
Part 2 – Declarations
The settings you might normally use in a stored procedure like SET NOCOUNT ON should be declared as soon as possible. You will probably want to declare at least two local variables as follows:
- One to hold the message itself. Event Notifications are actually XML fragments so you might choose to use either the XML type or one of the new large-object types like varbinary(max) instead. If I’m not doing any processing of the message as part of the Stored Procedure, I pick the varbinary(max) type.
- A variable of type UniqueIdentifier that will hold what is known as a Conversation Group ID. This is a Service Broker concept that allows us to relate many messages together as part of a long running conversation. For our purposes, we really only care about this here because when we pull messages out of the queue, a lock is placed on all other messages in the queue with the same Group Conversation ID while our Stored Procedure is running. This helps guarantee in-order delivery of messages – one the primary features of Service Broker.
So the next part of our stored procedure looks like this:
set nocount on
declare @msg varbinary(max),@cgid uniqueidentifier
Part 3 – Look for available messages
In any Service Broker application, a key to getting optimal performance is allow an already activated Stored Procedure to process as many messages as it can versus waiting for more instances of that stored procedure to get loaded and executing by SQL Server. It is also possible that because of that, by the time a Stored Procedure actually gets time to execute from the scheduler, there will not be any messages remaining for it to process. So we normally write the majority of a Service Program in an indefinite loop as well as Try-Catch block. We also do our work in a transaction scope. The transaction assure that we get an error we can roll the message back into the queue easily if an error occurs.
Once we have our “infrastructure” built-up, need to ask the Queue for the next available conversation handle. However, if you look at Queue (you can do that with a SELECT statement), you won’t see any column that exposes that. Instead, we need to use some special syntax. First, we normally write our special statement in a WAITFOR loop. This allows Service Broker some number of miliseconds to return the next available Group Conversation ID for the requested Queue. The WAITFOR also has a TIMEOUT setting. This allows our Stored Procedure to continue processing if, after timing out, no Group Conversation ID has been received. When the WAITFOR times out, we need to exit the Store Procedure as there are no messages in the Queue waiting to be processed. If, however, an ID is returned, processing needs to continue.
So the next chunk of our Stored Procedure looks like this:
begin try
begin tran;
while(1=1)
begin
set @cgid = null;
waitfor(get conversation group @cgid from dbo.EventQueue), timeout 10000;
if @cgid is null
begin
rollback;
break;
end
Part 4 – Get content from available messages
While there are many fields in a queue, we’re really only interested in one of them in this case – the message body. Earlier I noted that that one could use a SELECT statement to see the contents of a Queue. However, you cannot use an INSERT, UPDATE or DELETE statement on a Queue. So pull a message of our Queue, T-SQL 2005 adds a new keyword specifically for that task: RECEIVE. The RECEIVE statement has syntax like a SELECT statement, including a WHERE clause. In that WHERE clause, we specify that we only want messages for the Group Conversation ID we got in the previous step. All in all, this is fairly straight forward for us since we are only interested in the message sent to us by SQL Server in response to an event. However, since we’re only interested in processing one message at time here, we’ll just get the first one available using the TOP(1) modifier. Here’s the code:
;receive top(1) @msg = message_body from dbo.EventQueue where conversation_group_id = @cgid;
Part 5 – Message processing
Now that we have the message body, we can insert that into our CaughtEvents table rather simply as shown below. Since we’re also done with all of our processing we can commit the current transaction:
insert into dbo.caughtevents(message_type_name,eventdata) values (@mtn,@msg);
commit;
If you needed to additional processing of the message, you could add the appropriate code here.
Part 6 – Cleanup
At this point, we can need our while loop and close of the try section of the Stored Procedure. We should also add some graceful error handling. The new ERROR_x() functions make it easy to get detailed information about error and RAISERROR makes it easy to record that into the SQL Server logs. Note that Service Programs may run in different session IDs that the programs invoking them, so logging errors to the SQL Server logs is the most reliable way of capturing them.
Of course if there is still an active transaction, we should roll that back – it is most likely that such a transaction is the one we started to be make sure messages go back into the queue in the event of an error, after all.
end
end try
begin catch
declare @en int,@em nvarchar(max),@est int,@esv int,@el int,@ep nvarchar(128)
set @en = error_number();
set @em = error_message();
set @est = error_state();
set @esv = error_severity();
set @el = error_line();
set @ep = error_procedure();
if not(xact_state() = 0)
rollback;
raisError('%s:%d %s (%d)',@esv,@est,@ep,@el,@em,@en) with log;
end catch
end
go
Finishing up the Service Broker plumbing
So far we have talked a great deal about this thing called a Queue, but we haven’t created one, so that is the next step. Unlike defining a table, you do not define the columns in a view, but you may define the information Service Broker needs for Activation. For this example, we create a Queue called dbo.EventQueue like this:
create queue dbo.EventQueue with status=off,activation(procedure_name=dbo.ProcessEvent,max_queue_readers=5,execute as self);
That breaks down like this:
- Create queue dbo.EventQueue: catalogs the Queue
- With status=off: means that this Queue won’t start receiving messages immediately. I will show you how to “turn it on” shortly.
- Activation(…): contains the information Service Broker will use when to activate some number of stored procedures when a message arrives in this queue.
- Procedure_name=dbo.ProcessEvent: is how “bind” our Stored Procedure to this queue.
- Max_queue_readers: This setting specifies how many instances of the Stored Procedure previously listed will be fired up at a time to cope with messages coming into the queue. A typical setting is five.
- Execute as self: This setting means that the referenced Stored Procedure will be executed as the person who cataloged (created) the queue.
Although Service Broker messages are physically delivered into Queues, they are actually sent to another T-SQL object known as a Service. Why? It is a layer of indirection that allows one to easily move a Service Program and its queue from one instance of SQL Server to another without having to change code inside of other objects. In traditional Service Broker applications, we issue a SEND query to send a message to Service. The Service Broker then resolves the name of the service to an address in a fashion similar to how Domain Name Services (DNS) resolves the name for network hosts. This means, however, that Services need to have unique names over an entire network topology. Note that this doesn’t mean that each instance of the Service needs to have a unique name. Why? Messages are sent to Services by name so that any instance of the Service could receive and process the message. In this way, Service Broker applications can “scale-out” over many different machines with a minimum of code or configuration changes. You simply create the appropriate tables, queues, procedures and Services and update an internal routing table to bring a new server on-line. While that is usefully and our simple application does not need to take specific advantage of it, that does not mean we can safely give our service any name we like – plan ahead. Services are typically named in a URN format. These may look and seem like HTTP URLs, but they aren’t. Instead they are just unique names.
Along with a unique name, Services need two other elements to function: the name of the queue they represent and list of the Message Types they accept. Message Types are another Service Broker concept that our Event Notifications work needs to be code for but are not really in focus. If we borrow the “Class” concept from object-oriented programming, Message Types are really “Classes” that Messages sent to a Queue are instances of. The list of Message Types a Service can accept can grow long, so Service Broker supports the concept of a Contract. A Contract amounts to a list of Message Types that a particular Service will accept plus details on how Services exchange Messages. For the most part, we can ignore this except that we must tell Service Broker will accept Messages on a pre-defined contract specifically for Event Notifications. The T-SQL name of that Contract is -- like a Service name – expressed as an URN. So for our example, the statement we need to execute for cataloging the service is:
create service [http://staff.develop.com/ktegels/examples/auditEvent] on queue dbo.EventQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go
The Service name is [http://staff.develop.com/ktegels/examples/auditEvent] and the contract defining acceptable messages is [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification].
Creating Event Notifications
So far we’ve talked a fair about getting the needed Service Broker infrastructure created, and now its time to actually create our Event Notifications. Comparatively speaking, this is somewhat of anti-climax since we need to execute just a single statement. Let us start by dissecting one:
create event notification ServerEventNotification on server for AUDIT_LOGIN,AUDIT_LOGOUT,AUDIT_LOGIN_FAILED to service 'http://staff.develop.com/ktegels/examples/auditEvent','current database'
go
· Create Event Notification ServerEventNotification: This tells SQL Server that we are cataloging an Event Notification and gives that notification the name ServerEventNotification. Note that since this is not part of the Service Broker “plumbing,” we do not need to give it a URN-style name.
· On server: There are actually two flavors of Event Notifications, one for Server Level events like Log-ins and Log-outs, creating/dropping databases and similar events. The other type of notifications are scoped to a database and cover events like creating and dropping tables and view. We will examine an Event Notification specifically for that in the next section.
· For AUDIT_LOGIN,…: This represents a list of SQL Server events that this notification listens for. When events of these types occur, the Event Notification marshals up the data for that event into an XML fragment and SENDs it as message to the Service named following.
· To service …: This names the service to receive the Event Notification message.
· ‘current database’: This setting essentially tells Service Broker to ignore its standard Service name to address resolution protocol and just look in the current database for the Service.
The only significant differences between an instance-level Event Notification (which is cataloged with ON SERVER) and a database-level Event Notification are:
- Instead of …on server…, one uses …database…. The current database is assumed when cataloging.
- The list of listen-able events is different.
For example, here is how we could use the infrastructure we have already constructed to catch the creation or deletion of a table. Note that you could use a DDL Trigger to do the same thing here without the overhead of creating most of the Service Broker “plumbing” we have covered above.
create event notification DatabaseEventNotification on database for DDL_TABLE_EVENTS to service 'http://staff.develop.com/ktegels/examples/auditEvent','current database'
And that is really about all there is to it. All we need to do is enable the queue to start receiving message and give it a good test run. To enable a queue, simply alter it like this:
alter queue dbo.EventQueue with status=on
go
You can easily test verify that all this works. Simply start up a command prompt (start|run|cmd) and then start the SQLCMD utility – something like this:
sqlcmd –S . –E
At the 1> prompt type:
Create table dbo.foo(bar int)
Go
Drop table dbo.foo
Go
Exit
Then back in SSMS (or whatever other tool you used to catalog the previous work), execute these queries:
select * from dbo.caughtevents
select * from sys.transmission_queue
select * from dbo.eventqueue
The first query should show you all of the event caught from when you logged in using the SQLCMD utility, your table creation and drop and your logout. If not, the very last column of the sys.transmission_queue should give information about why messages are not being delievered to the queue as expected. You might even be quick enough to see messages sitting in the dbo.EventQueue queue waiting for an instance of dbo.ProcessEvents to come along and read them.
So have we achieved our goal of having triggers for logins and logouts? I guess that depends on how you define “trigger.” If by syntax alone, no. But if by affect, we have. The nice thing about this is that it gives us a pattern for solving other types of event-driven needs. At the core of this is understanding that Event Notifications and Service Broker can be used together to accomplish that goal.
If you'd like the source code for all of this, it is downloadable from here.