tsql - 使用某些链接服务器组件执行触发器

Question

我在 [Server1].[DatabaseA].[dbo].[EMRVisit] 中插入了一行。我需要从链接服务器 [Server2].[DatabaseB] 向访问记录添加一些信息，因此我设置了一个 AFTER INSERT 触发器。

两台服务器都是 MSSQL 2016，数据库目前以 2008 兼容性运行。

目前，触发器正在阻止插入行。除了插入尝试和回滚之外，我在 Profiler 中看不到任何内容。此外，它不会产生任何其他错误。代码在触发器之外运行良好。

令人沮丧的部分是触发器运行良好，就像在旧的 2008 盒子上一样。

ALTER TRIGGER [dbo].[Pt_Note_Edit]
   ON  [dbo].[EMRVisit]
   AFTER INSERT
AS 

IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
BEGIN

    DECLARE @med_rec_nbr varchar(15), @Days VARCHAR(20), @OldNote nvarchar(MAX), @NewNote nvarchar(MAX)

    SET @med_rec_nbr = (SELECT ept.PatientChartNumber FROM INSERTED ev INNER JOIN [Server1].[DatabaseA].[dbo].[Patients] ept ON ev.PtID = ept.PtID)

--Collect ADS Data

--Convert @med_rec_nbr to @Account
    DECLARE @Account varchar(15)

    EXEC [Server2].[DatabaseB].[dbo].ADS_CleanUp_MRN @med_rec_nbr, @Account=@Account OUTPUT

    SELECT *
    INTO #PtChgs
    FROM [Server2].[DatabaseB].[dbo].ADS_Charges
    WHERE AccountNo = @Account

    --Get Old Pt Note
    SELECT @OldNote = ept.PatientNote
    FROM [Server1].[DatabaseA].[dbo].[Patients] ept
    WHERE ept.PatientChartNumber = @med_rec_nbr

    --Edit OldNote
    DECLARE @NotePart nvarchar(MAX), @Trans Int

    SET @NotePart = CASE
        WHEN @OldNote IS NOT NULL THEN SUBSTRING(@OldNote,CHARINDEX('~',@OldNote,1)+1,LEN(@OldNote))
        ELSE ''
    END


    --Get Latest Post Op Charges
SELECT Charges.FromDate
        ,Charges.Modifiers
        ,DATEDIFF(DAY, Charges.FromDate, GETDATE()) AS DaysIn
INTO #PtPostOp
FROM [Server2].[DatabaseB].[dbo].[Charges] Charges
JOIN [Server2].[DatabaseB].[dbo].[ProcedureCodes] PC ON Charges.Cpt = PC.Code
WHERE Charges.AccountNo = @Account
AND Charges.FromDate > CONVERT(VARCHAR, DATEADD (DAY , -(PC.NumberOfDays + 10) , GETDATE()), 112)
AND PC.NumberOfDays <> 0
ORDER BY Charges.FromDate DESC

DECLARE @FromDate integer,@Modifiers VARCHAR(5), @DaysIn VARCHAR (2), @Enc_Rows int, @Mod varchar(3)

--Get count of encounters in Post Op period for current patient
SET @Enc_Rows = 0
SELECT @Enc_Rows = COUNT(*) FROM #PtPostOp

    --Loop through records and concatenate rows
    SET @Days = ''

        WHILE @Enc_Rows > 0
        BEGIN
            --Get first record

            SELECT TOP 1 @FromDate = FromDate
                        ,@Modifiers = CASE 
                                            WHEN Modifiers LIKE '%RT%' THEN 'OD'
                                            WHEN Modifiers LIKE '%LT%' THEN 'OS'
                                            ELSE Modifiers
                                            END
                        ,@DaysIn = DaysIn
            FROM #PtPostOp
            ORDER BY FromDate DESC

            --Concatenate Row

            SET @Days = @Days + @Modifiers + ' ' + @DaysIn + ' days'
            
            IF @Enc_Rows > 1
                SET @Days = @Days + ': '
        
            SET @Enc_Rows = @Enc_Rows -1

            DELETE FROM #PtPostOp WHERE FromDate = @FromDate

        END
        
    IF @Days <> ''
        SET @NewNote = ('**POST OP ' + @Days + ' ** ~' + @NotePart)
        ELSE
        SET @NewNote = @NotePart


    --Update the PatientNote
    UPDATE [Server1].[Database].[dbo].[Patients] 
    SET PatientNote = @NewNote
    WHERE PatientChartNumber = @med_rec_nbr

END

除此之外-我还尝试在我的代码中添加一个try-catch，但这会产生DTC错误。

标签： tsqltriggerslinked-server