Sending Service Broker Message on Trigger

Sample Trigger to send Service Broker message on data change

CREATE TRIGGER [Person].[TRG_Person_Contact]
ON [Person].[Contact]
FOR DELETE,INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @XMLDOC XML
DECLARE @XMLTEXT NVARCHAR(MAX)

DECLARE @ContactID [int]
DECLARE @NameStyle [bit]
DECLARE @Title [nvarchar] (8)
DECLARE @FirstName [nvarchar] (50)
DECLARE @MiddleName [nvarchar] (50)
DECLARE @LastName [nvarchar] (50)
DECLARE @Suffix [nvarchar] (10)
DECLARE @EmailAddress [nvarchar] (50)
DECLARE @EmailPromotion [int]
DECLARE @Phone [nvarchar] (25)
DECLARE @PasswordHash [varchar] (128)
DECLARE @PasswordSalt [varchar] (10)
DECLARE @AdditionalContactInfo [xml]
DECLARE @rowguid [uniqueidentifier]
DECLARE @ModifiedDate [datetime]

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

-- This cursor for inserted or updated records

IF @ACT = 'INSERT' OR @ACT = 'UPDATE'
BEGIN
DECLARE curINSERTED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[INSERTED].[ContactID]
,[INSERTED].[NameStyle]
,[INSERTED].[Title]
,[INSERTED].[FirstName]
,[INSERTED].[MiddleName]
,[INSERTED].[LastName]
,[INSERTED].[Suffix]
,[INSERTED].[EmailAddress]
,[INSERTED].[EmailPromotion]
,[INSERTED].[Phone]
,[INSERTED].[PasswordHash]
,[INSERTED].[PasswordSalt]
,[INSERTED].[AdditionalContactInfo]
,[INSERTED].[rowguid]
,[INSERTED].[ModifiedDate]
FROM
[INSERTED]

OPEN curINSERTED
FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curINSERTED
DEALLOCATE curINSERTED
END

-- This cursor for deleted records

IF @ACT = 'DELETE'
BEGIN
DECLARE curDELETED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[DELETED].[ContactID]
,[DELETED].[NameStyle]
,[DELETED].[Title]
,[DELETED].[FirstName]
,[DELETED].[MiddleName]
,[DELETED].[LastName]
,[DELETED].[Suffix]
,[DELETED].[EmailAddress]
,[DELETED].[EmailPromotion]
,[DELETED].[Phone]
,[DELETED].[PasswordHash]
,[DELETED].[PasswordSalt]
,[DELETED].[AdditionalContactInfo]
,[DELETED].[rowguid]
,[DELETED].[ModifiedDate]
FROM
[DELETED]

OPEN curDELETED
FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curDELETED
DEALLOCATE curDELETED
END

END
go