Tested with .Net 2.0.40607.42 and SQL Server 2005 9.00.852
Not yet completed...coming soon...
-- Tested with SQL Server 2005 9.00.852
/*
This script creates a number of objects that are necessary
to illustrate how an asynchronous Service Broker trigger
can be used. It can be executed all in one batch or in the
proper and sequential order. There are times when you'll
want to record updates made to a table by saving those
updates to a historical table. The updates can later be
reviewed from the historical table for whatever reason.
Part of the problem with SQL Server 2000 and older triggers
is that they run synchronously - they run in the same
context as that of the executor. Accordingly, an update
made to a table with an UPDATE trigger may take a
long to finish, simply because it's waiting for the
trigger to complete its work. With asynchronous triggers
in SQL Server 2005 and Service Broker, this 'wait time'
is greatly reduced. Now, you can simply send a Service
Broker message containing the updated records to a queue,
and have a seperate service program (in this case a
stored procedure) that retrieves those records and saves
them into a historical table.
It may not be a good idea to create an asynchronous trigger
for the mere purpose of saving historical data, since simply
adding records to a local table is pretty fast to begin with;
however, you will want to create one for use with a
long-running process or statement. It then becomes
beneficial to use asynchronous triggers in such cases where
the trigger might take up a lot of the executing context's
time.
Table with trigger:
tblDotNetFunTriggerTest
Table with history of updates made to preceding table:
tblDotNetFunTriggerTestHistory
*/
-- Note: this script tested against a
-- SQL_Latin1_General_CP1_CI_AS collation.
/* -------------------------------------------------------------------------
DROP ALL RELATED OBJECTS:
------------------------------------------------------------------------- */
IF EXISTS(SELECT * FROM sys.triggers
WHERE name = 'tg_tblDotNetFunTriggerTest_Update')
DROP TRIGGER tg_tblDotNetFunTriggerTest_Update;
IF EXISTS(SELECT * FROM sys.tables
WHERE name = 'tblDotNetFunTriggerTestHistory')
DROP TABLE tblDotNetFunTriggerTestHistory;
IF EXISTS(SELECT * FROM sys.tables
WHERE name = 'tblDotNetFunTriggerTest')
DROP TABLE tblDotNetFunTriggerTest;
IF EXISTS(SELECT * FROM sys.services
WHERE name = 'DotNetFunTriggerTestService')
DROP SERVICE DotNetFunTriggerTestService;
IF EXISTS(SELECT * FROM sys.service_contracts
WHERE name = 'DotNetFunTriggerTestContractSentByAny')
DROP CONTRACT DotNetFunTriggerTestContractSentByAny;
IF EXISTS(SELECT * FROM sys.service_message_types
WHERE name = 'DotNetFunTriggerTestMessageTypeXML')
DROP MESSAGE TYPE DotNetFunTriggerTestMessageTypeXML;
IF EXISTS(SELECT * FROM sys.service_queues
WHERE name = 'DotNetFunTriggerTestQueue')
DROP QUEUE DotNetFunTriggerTestQueue;
IF EXISTS(SELECT * FROM sys.procedures
WHERE name = 'sp_DotNetFunTriggerTestServiceProgram')
DROP PROC sp_DotNetFunTriggerTestServiceProgram;
GO
/* -------------------------------------------------------------------------
CREATE THE SERVICE BROKER MESSAGE TYPE
------------------------------------------------------------------------- */
CREATE MESSAGE TYPE DotNetFunTriggerTestMessageTypeXML
VALIDATION = WELL_FORMED_XML;
GO
/* -------------------------------------------------------------------------
CREATE THE SERVICE BROKER CONTRACT
------------------------------------------------------------------------- */
CREATE CONTRACT DotNetFunTriggerTestContractSentByAny
(DotNetFunTriggerTestMessageTypeXML SENT BY ANY);
GO
/* -------------------------------------------------------------------------
CREATE THE SERVICE BROKER SERVICE PROGRAM
------------------------------------------------------------------------- */
CREATE PROC sp_DotNetFunTriggerTestServiceProgram
AS
-- This procedure will get triggered automatically
-- when a message arrives at the
-- DotNetFunTriggerTestQueue queue.
-- Let's retrieve any messages sent to us here:
DECLARE @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ID INT,
@COL2 VARCHAR(MAX);
DECLARE @Queue TABLE (
MessageBody VARBINARY(MAX),
MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE message_body, message_type_name
FROM DotNetFunTriggerTestQueue
INTO @Queue
), TIMEOUT 5000;
-- If no messages exist, then break out of the loop:
IF NOT EXISTS(SELECT * FROM @Queue) BREAK;
DECLARE c_DotNetFunTriggerTest CURSOR FAST_FORWARD
FOR SELECT * FROM @Queue;
OPEN c_DotNetFunTriggerTest;
FETCH NEXT FROM c_DotNetFunTriggerTest
INTO @MessageBody, @MessageTypeName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Let's only deal with messages of Message Type
-- DotNetFunTriggerTestMessageTypeXML:
IF @MessageTypeName = 'DotNetFunTriggerTestMessageTypeXML'
BEGIN
SET @XML = CAST(@MessageBody AS XML);
-- Now let's save the XML records into the
-- historical table:
INSERT INTO tblDotNetFunTriggerTestHistory
SELECT tbl.rows.value('@ID', 'INT') AS ID,
tbl.rows.value('@COL2', 'VARCHAR(MAX)') AS COL2,
GETDATE() AS UPDATED
FROM @XML.nodes('/inserted') tbl(rows);
END
FETCH NEXT FROM c_DotNetFunTriggerTest
INTO @MessageBody, @MessageTypeName;
END
CLOSE c_DotNetFunTriggerTest;
DEALLOCATE c_DotNetFunTriggerTest;
-- Purge the temporary in-proc table:
DELETE FROM @Queue;
END
GO
/* -------------------------------------------------------------------------
CREATE THE SERVICE BROKER QUEUE
------------------------------------------------------------------------- */
CREATE QUEUE DotNetFunTriggerTestQueue
WITH STATUS = ON,
RETENTION = OFF,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = sp_DotNetFunTriggerTestServiceProgram,
MAX_QUEUE_READERS = 5,
EXECUTE AS OWNER
);
GO
/* -------------------------------------------------------------------------
CREATE THE SERVICE BROKER SERVICE
------------------------------------------------------------------------- */
CREATE SERVICE DotNetFunTriggerTestService
ON QUEUE DotNetFunTriggerTestQueue
(DotNetFunTriggerTestContractSentByAny);
GO
/* -------------------------------------------------------------------------
CREATE THE MAIN TEST TABLE
------------------------------------------------------------------------- */
CREATE TABLE tblDotNetFunTriggerTest (
ID INT IDENTITY PRIMARY KEY,
COL2 VARCHAR(MAX) NULL
);
GO
/* -------------------------------------------------------------------------
CREATE THE HISTORICAL TEST TABLE
------------------------------------------------------------------------- */
CREATE TABLE tblDotNetFunTriggerTestHistory (
ID INT NOT NULL,
COL2 VARCHAR(MAX) NULL,
UPDATED DATETIME DEFAULT GETDATE()
);
GO
/* -------------------------------------------------------------------------
CREATE THE TRIGGER
------------------------------------------------------------------------- */
CREATE TRIGGER tg_tblDotNetFunTriggerTest_Update
ON tblDotNetFunTriggerTest
FOR UPDATE
AS
-- We don't want the following T-SQL to affect the
-- UPDATE, so let's place it in a different
-- transaction; if the following code raises an error,
-- only it will be rolled back:
SET XACT_ABORT OFF;
-- Send an asynchronous message to a Service Broker queue
-- with the affected records:
-- TO MAKE THIS A TRUELY ASYNCHRONOUS TRIGGER, WE MUST
-- PERFORM AS LITTLE WORK AS POSSIBLE HERE AND WITHOUT
-- TYING UP THE RESOURCES OF THE TRIGGER.
DECLARE @XML XML;
IF EXISTS(SELECT * FROM inserted)
BEGIN
BEGIN TRAN;
BEGIN TRY
-- Save the records as XML
SELECT @XML = (SELECT * FROM inserted FOR XML AUTO);
-- Send the XML records to the Service Broker queue:
DECLARE @DialogHandle UNIQUEIDENTIFIER,
@ConversationID UNIQUEIDENTIFIER;
/*
The target Service Broker service is the same
service as the initiating service; however, you
can set up this type of trigger to send messages
to a remote server or another database.
*/
BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE DotNetFunTriggerTestService
TO SERVICE 'DotNetFunTriggerTestService'
ON CONTRACT DotNetFunTriggerTestContractSentByAny;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE DotNetFunTriggerTestMessageTypeXML
(@XML);
-- Let's detect an error state for this dialog
-- and rollback the entire transaction if one is
-- detected:
IF EXISTS(SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @DialogHandle
AND state = 'ER')
RAISERROR('Dialog in error state.', 18, 127);
ELSE
BEGIN
-- We don't care about a reply from the target, so let's
-- end the conversation here:
END CONVERSATION @DialogHandle;
COMMIT TRAN;
END
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @ERRMSG VARCHAR(MAX);
SET @ERRMSG = ERROR_MESSAGE();
RAISERROR(@ERRMSG, 18, 127);
END CATCH;
END
GO
/* -------------------------------------------------------------------------
THE TEST STARTS HERE
------------------------------------------------------------------------- */
-- Insert two rows into the table.
INSERT INTO tblDotNetFunTriggerTest (COL2) VALUES ('HELLO WORLD!');
INSERT INTO tblDotNetFunTriggerTest (COL2) VALUES ('HELLO WORLD!');
-- Now let's update both rows to fire the UPDATE trigger:
UPDATE tblDotNetFunTriggerTest SET COL2 = 'OK once.....';
UPDATE tblDotNetFunTriggerTest SET COL2 = 'OK twice.....';
-- Now let's see if the updates arrived in our historical table:
-- Let's give the Service Program (SPROC) time to process the
-- message(s) (3 seconds):
WAITFOR DELAY '00:0:03';
select * from tblDotNetFunTriggerTestHistory;
Current .Net Version: 2.0.50727
