SQL Server触发器 - 将消息发送到队列

-- 创建一个链接服务器，用于连接到服务总线队列
EXEC sp_addlinkedserver 
   @server= 'myqueue', 
   @srvproduct= '', 
   @provider= 'SQLNCLI', 
   @provstr= 'Data Source=myqueue.servicebus.windows.net;Initial Catalog=myqueue;User ID=myusername;Password=mypassword;'

-- 创建一个触发器，在插入新的订单时将订单信息发送到队列
CREATE TRIGGER tr_insert_order
ON orders
AFTER INSERT
AS
BEGIN
   DECLARE @order_id INT
   SELECT @order_id = order_id FROM inserted

   DECLARE @message NVARCHAR(MAX)
   SET @message = N'INSERT INTO orders (order_id, customer_id, order_date) VALUES (' + CAST(@order_id AS NVARCHAR) + N', 1, GETDATE())'

   EXEC sp_tables 'myqueue'

   EXEC('
      INSERT INTO OPENROWSET(''SQLNCLI'', ''Data Source=myqueue.servicebus.windows.net;Initial Catalog=myqueue;User ID=myusername;Password=mypassword'', ''EXEC sp_send_dbmail @recipients=N''myemail@example.com'', @subject=N''New order'', @body=N''' + @message + N''' '')
   ')
END