请求协助创建SQL Server INSERT触发器，将插入数据发送至远程URL

Hey there! As someone new to SQL Server, I totally get wanting to set up a trigger to push inserted data to your web service in real-time. Let's walk through this step by step, including key things to watch out for so you don't run into common pitfalls.

First, a critical heads-up: SQL Server triggers run synchronously. That means if your web service is slow to respond, the original insert operation will block until the HTTP request finishes. If this would hurt your application's performance, you might want to consider an asynchronous approach later (like using Service Broker queues), but let's start with the basic synchronous trigger setup.

Option 1: CLR Trigger (Recommended for Stability & Control) #

T-SQL doesn't have native HTTP request support, so using a CLR trigger (which lets you write C#/VB.NET code) is the most reliable way to handle this. Here's how to set it up:

Step 1: Enable CLR Integration in SQL Server #

First, you need to turn on CLR support (it's disabled by default):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

For SQL Server 2017+, you'll also need to relax strict CLR security (for testing; in production, use signed assemblies instead):

sp_configure 'clr strict security', 0;
RECONFIGURE;

Step 2: Write the C# Code for the Trigger #

Create a C# class library project in your preferred editor with this code. It reads the inserted data and sends a POST request to your web service:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Text;

public class WebServicePushTrigger
{
    [SqlTrigger(Name = "PushInsertedDataToWebService", Target = "YourTableName", Event = "FOR INSERT")]
    public static void SendDataToWebService()
    {
        var triggerContext = SqlContext.TriggerContext;
        if (triggerContext.TriggerAction != TriggerAction.Insert)
            return;

        // Connect back to the database to get inserted rows
        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            var cmd = new SqlCommand("SELECT * FROM INSERTED", conn);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // Build JSON payload (adjust fields to match your web service's schema)
                    var json = $"{{\"Id\": {reader["Id"]}, \"Name\": \"{reader["Name"].ToString().Replace("\"", "\\\"")}\"}}";

                    // Send POST request
                    var webServiceUrl = "https://your-web-service-url.com/api/your-endpoint";
                    using (var client = new WebClient())
                    {
                        client.Headers[HttpRequestHeader.ContentType] = "application/json";
                        try
                        {
                            var response = client.UploadString(webServiceUrl, "POST", json);
                            // Optional: Log the response to a table for debugging
                            // LogRequestResult(response, "Success");
                        }
                        catch (Exception ex)
                        {
                            // Optional: Log errors to a table
                            // LogRequestResult(ex.Message, "Failed");
                            // Uncomment below if you want the insert to fail on web service error
                            // throw;
                        }
                    }
                }
            }
        }
    }

    // Optional helper for logging (create a TriggerLogs table first)
    // private static void LogRequestResult(string message, string status)
    // {
    //     using (var conn = new SqlConnection("context connection=true"))
    //     {
    //         conn.Open();
    //         var cmd = new SqlCommand(
    //             "INSERT INTO TriggerLogs (Status, Message, LogTime) VALUES (@status, @msg, GETDATE())",
    //             conn);
    //         cmd.Parameters.AddWithValue("@status", status);
    //         cmd.Parameters.AddWithValue("@msg", message);
    //         cmd.ExecuteNonQuery();
    //     }
    // }
}

Step 3: Deploy the DLL to SQL Server #

Compile the project to a DLL, then run these SQL commands to register the assembly and create the trigger:

-- Create the assembly from your DLL file
CREATE ASSEMBLY WebServicePushAssembly
FROM 'C:\Path\To\Your\WebServicePushTrigger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS; -- Needed for HTTP requests

-- Create the trigger that calls the CLR method
CREATE TRIGGER PushInsertedDataToWebService
ON YourTableName
AFTER INSERT
AS EXTERNAL NAME WebServicePushAssembly.WebServicePushTrigger.SendDataToWebService;

Option 2: T-SQL + xp_cmdshell (Quick Test, Not Recommended for Production) #

If you don't want to mess with CLR, you can use xp_cmdshell to call curl for HTTP requests. Note: This has security risks and is less reliable for production use.

Step 1: Enable xp_cmdshell #

First, turn on this advanced feature:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Step 2: Create the T-SQL Trigger #

This trigger loops through inserted rows, builds a curl command, and executes it:

CREATE TRIGGER PushInsertedDataToWebService
ON YourTableName
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Id INT, @Name NVARCHAR(100), @Json NVARCHAR(MAX), @Cmd NVARCHAR(MAX);

    -- Cursor to handle multiple inserted rows
    DECLARE InsertedRows CURSOR FOR
    SELECT Id, Name FROM INSERTED;

    OPEN InsertedRows;
    FETCH NEXT FROM InsertedRows INTO @Id, @Name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Build JSON payload (escape quotes to avoid syntax issues)
        SET @Json = N'{"Id": ' + CAST(@Id AS NVARCHAR) + N', "Name": "' + REPLACE(@Name, N'"', N'\"') + N'"}';

        -- Build curl command
        SET @Cmd = N'curl -X POST -H "Content-Type: application/json" -d ''' + @Json + N''' "https://your-web-service-url.com/api/your-endpoint"';

        -- Execute the command
        EXEC xp_cmdshell @Cmd;

        FETCH NEXT FROM InsertedRows INTO @Id, @Name;
    END

    CLOSE InsertedRows;
    DEALLOCATE InsertedRows;
END

Key Tips for Success #

• Async Alternative: If web service latency is a concern, use SQL Server Service Broker to queue the data and process it asynchronously. This way, inserts don't block waiting for the web service.
• Error Handling: Always add logging (like the optional LogRequestResult method) so you can debug failed requests without losing visibility.
• Permissions: Make sure the SQL Server service account has network access to your web service. For CLR triggers, the assembly needs EXTERNAL_ACCESS permission.
• Test First: Try this in a staging environment before deploying to production to avoid breaking your insert operations.

内容的提问来源于stack exchange，提问作者Pawan Developers