In the previous post we created a class library which sends a message to an Azure Service Bus topic. In this part we will load our assembly in SQL Server and create the required DML triggers to act on changes to our
Loading our assembly
Before we can load our assembly we need to make sure that SQL Server CLR integration is enabled.
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
Make sure the class library containing our method is accessible from within SQL Server. This means that SQL Server needs to have access to the folder containing our DLL file on disk. Since the library is signed, we will create an asymmetric key and a login which allows access to our class library.
USE [master]; GO CREATE ASYMMETRIC KEY [AzureKey] FROM EXECUTABLE FILE = '<path to the DLL file created in part 1>' CREATE LOGIN [AzureLogin] FROM ASYMMETRIC KEY [AzureKey]; GRANT EXTERNAL ACCESS ASSEMBLY TO [AzureLogin]; GO
Finally, we can load our assembly so SQL Server can access it.
CREATE ASSEMBLY [AzureStoredProcedures] FROM '<path to the DLL file created in part 1>' WITH PERMISSION_SET = UNSAFE; GO
Create the stored procedure
When the assembly has successfully loaded, we can create the stored procedure which will call our CLR method and send the message to the Azure Service Bus.
CREATE PROCEDURE [dbo].[SendToAzure] @contractId [int], @operation [nvarchar](50), @returnValue [nvarchar](2000) OUTPUT WITH EXECUTE AS CALLER AS EXTERNAL NAME [AzureStoredProcedures].[AzureStoredProcedures.StoredProcedures].[SendMessage] GO
The external name is divided into three parts:
- AzureStoredProcedures: the name we gave to the loaded class library.
- AzureStoredProcedures.StoredProcedures: the namespace and the name of our class within the loaded class library. If your class is not inside a namespace, you can skip the namespace part.
- SendMessage: the name of the method which sends the event to the Azure Service Bus.
Create the DML triggers
Since we want to act on the three major DML operations (insert, update and delete) we will need two triggers: one for the insert and update operations, and one for the delete operation. We want to use the id of the modified contract and for insert and update operations we can easily get this from the
INSERTED table, but for delete operations we need to get this from the
DELETED table. I’m also using an
operation string so that my subscription handler has a notion of what kind of operation has occurred. A final thing to mention is that in the sample below I am simply selecting the response that is returned from our CLR method. In a real world example I would insert this into an error table which is monitored, so that any errors that might occur during the web service post are correctly handled by the team.
CREATE TRIGGER [dbo].[Trigger_Contracts_IU] ON [dbo].[Contracts] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @ContractId [int]; DECLARE @Response [nvarchar](2000); SELECT @ContractId = inserted.[Id] from inserted; EXEC [dbo].[SendToAzure] @ContractId, 'modified', @Response OUT; SELECT @Response; END GO ALTER TABLE [dbo].[Contracts] ENABLE TRIGGER [Trigger_Contracts_IU] GO
CREATE TRIGGER [dbo].[Trigger_Contracts_D] ON [dbo].[Contracts] FOR DELETE AS BEGIN SET NOCOUNT ON; DECLARE @ContractId [int]; DECLARE @Response [nvarchar](2000); SELECT @ContractId = deleted.[Id] from deleted; EXEC [dbo].[SendToAzure] @ContractId, 'removed', @Response OUT; SELECT @Response; END GO ALTER TABLE [dbo].[Contracts] ENABLE TRIGGER [Trigger_Contracts_D] GO
Now, anytime a record in the
Contracts table is inserted, updated or deleted, the trigger fires and executes our stored procedure which in turn calls our method which will send a message to the Azure Service Bus.
If we add something we can use the Service Bus Explorer to peek into the messages that are ready to be handled and see the contents.