SQL Service Broker

Question

我已经创建了一个服务代理，它将id存储在队列表中。但问题是，当我想在存储过程中重新获得id时，它现在被格式化为xml。因为service broker消息是XML。

我怎么能每次都只获取最后一个Id，因为触发器是在更新行之后触发的。

在我的代码=>下面

/****** Object:  Trigger [dba].[TriggerCall]    Script Date: 6/16/2015 2:55:57 PM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dba].[TriggerCall] ON  [dba].[CallID] FOR     UPDATE 
AS 

BEGIN
    SET NOCOUNT ON;
    DECLARE @MessageBody XML  
    DECLARE @ID varchar(50)
    -- Insert statements for trigger here

                --get relevant information from inserted/deleted and convert to xml message  
        SET @MessageBody = (SELECT Id FROM inserted  
        FOR XML AUTO)          

                    If (@MessageBody IS NOT NULL)  
        BEGIN 

                    DECLARE @Handle UNIQUEIDENTIFIER;   
                    BEGIN DIALOG CONVERSATION @Handle   
                    FROM SERVICE [TestServiceInitiator]   
                    TO SERVICE 'TestServiceTarget'   
                    ON CONTRACT [TestContract]   
                    WITH ENCRYPTION = OFF;   
                    SEND ON CONVERSATION @Handle   
                    MESSAGE TYPE [TestMessage](@MessageBody);
        END

END



/****** Object:  StoredProcedure [dbo].[usp_GetCall]    Script Date: 6/16/2015 2:44:27 PM ******/

SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetCall]
AS
BEGIN 
DECLARE @message_type varchar(100) 
DECLARE @dialog uniqueidentifier, @message_body XML; 
Declare @Object as Int; 
Declare @URL as varchar(255)
Declare @ResponseText as Varchar(8000);
Declare @ID as Varchar(38);

WHILE (1 = 1) 
        BEGIN -- Receive the next available message from the queue 
        WAITFOR ( 
                    RECEIVE TOP(1) @message_type = message_type_name,     
                    @message_body = CAST(message_body AS XML),     
                    @dialog = conversation_handle

        FROM dbo.TestQueue ), TIMEOUT 500    if (@@ROWCOUNT = 0 OR @message_body IS NULL) 

        BEGIN 

                    BREAK 
        END 
              ELSE 
                    BEGIN 

INSERT INTO [dbo].[testtabel]
       ([id]
       ,[callid],
       [test])
 VALUES
       ('111', '111', @message_body)


                    END
        END CONVERSATION @dialog 
        END
END

Answer 1

如果我没理解错的话，您只是希望将ID恢复为int。如果是这种情况，则应执行以下操作：

use tempdb;
create table inserted (id int);
insert into inserted values (1);
declare @message_body xml;

set @message_body= (select * from inserted for xml auto);
select @message_body.value('(/inserted/@id)[1]', 'int');

神奇的是最后一行(剩下的部分只是为我测试而设置的)。

不过，我想借此机会澄清一下你似乎有的一个误解。SQL server中的触发器不是按行触发的，而是按批触发的。因此，如果您对表运行update并更新了50行，则触发器将触发一次，并且插入(和删除)的表中将包含50行。只是一些需要考虑的因素。