sql-server - 从触发器执行时 SQL 语句未终止

Question

当更新特定列时，我做了一个小解决方案来将客户数据行导出到 csv 文件。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER dbo.EXPORT_CUSTOMER_TYPE 
   ON  dbo.ADDRESSES 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF (UPDATE(SYMBOL0))
    BEGIN
        DECLARE @ADR_ID INT = (SELECT TOP 1 ID FROM inserted)
        
        -- TEMPORARY TABLE TO STORE THE COLUMNS WHICH SHOULD BE EXPORTED
        declare @ExportCols TABLE (
            idx int identity(1, 1),
            colName nvarchar(255)
        )

        -- READ INI FILE TO GET THE COMMA SEPARATED COLUMN NAMES
        declare @cols_dirtyString nvarchar(50);
        declare @outputfile nvarchar(255);
        exec dbo.lsp_ReadIniFile
            'C:\temp\SQL_READ_FILE_TEST\config.ini', 'ExportConfig', 'ExportColumns', @cols_dirtyString OUTPUT

        exec dbo.lsp_ReadIniFile
            'C:\temp\SQL_READ_FILE_TEST\config.ini', 'ExportConfig', 'OutputFile', @outputfile OUTPUT

        set @outputfile = REPLACE(REPLACE(@outputfile, '{date}', CONVERT(VARCHAR, GETDATE(), 23)), '{id}', CAST(@ADR_ID AS NVARCHAR))

        -- FILL THE TEMPORARY TABLE WITH COLUMN NAMES AND TEMPORARY IDS 
        INSERT INTO @ExportCols
        SELECT * FROM string_split(@cols_dirtyString, ',')

        -- LOOP THROUGH THE TEMP TABLE AND BUILD THE QUERY STRING
        declare @index int = 1;
        declare @rowcount int = (select count(*) from @ExportCols);
        declare @statement nvarchar(max) = 'SELECT '

        while (@index <= @rowcount)
        begin
            declare @colName nvarchar(255) = (select colName from @ExportCols where idx = @index)
            set @statement = @statement + @colName + ', '
            set @index = @index + 1;
        end

        set @statement = substring(@statement, 0, len(@statement))
        set @statement = @statement + ' FROM CobraDemoData.dbo.ADDRESSES WHERE ID = ' + CAST(@ADR_ID AS NVARCHAR)

        -- EXECUTE THE BUILT QUERY STRING
        DECLARE @ExportSQL nvarchar(max) = N'EXEC master.dbo.xp_cmdshell ''bcp "' + @statement + '" queryout "' + @outputfile + '" -T -c -t ";" -S T470p-DH\DEV'''
        EXEC(@ExportSQL)


    END
END
GO

需要注意的几件事。我知道这不是解决这个要求的好方法。客户希望这样做。我别无选择。我也知道目前该脚本仅在插入的表只有一行时才有效。这是一个早期状态，将得到解决。:)

如果我在触发器内部执行逻辑，则文件将按照我想要的方式导出必要的数据。但是，一旦我将它放入触发器并更新一行，该语句就永远不会完成。该文件已创建，但永久“使用中”。我通常不使用 tsql，所以任何帮助将不胜感激！

标签： sql-servertsql