SQL Server 2005 触发的审核表已移至 SQL Server 2008，现在尝试将行插入审核表时触发器不响应

Question

我们从 SQL Server 2005 数据库和表开始。 [UPDATE、INSERT 和 DELETE] 在这种情况下，当应用程序 (VB6) 数据表被修改时，我们使用 UPDATE 触发器将行插入到审计表中。我们将审核表移至 SQL Server 2008。触发器语句（在 SQL Server 2005 上）的唯一更改是我们将原始 ([FHA-4]) 修改为新的 (SQL Server 2008 [FHA-DMZ- CL1SQL]) 服务器名称。

当触发器被激活时，沙漏将保持打开状态，直到出现 sql 超时消息并且应用程序中止。检查审计表时，没有添加任何新内容，因此插入不起作用。

这是该表的实际触发语句：

USE [BCC_DHMH]
GO
/****** Object:  Trigger [dbo].[TriggerAddressUpdate]    Script Date: 04/07/2010       09:47:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Logic to save to the table that supports Tripwire
ALTER       TRIGGER [dbo].[TriggerAddressUpdate] 
   ON  [dbo].[tblAddress]
   AFTER UPDATE
AS 

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
 SET NOCOUNT ON;

 --IF (SYSTEM_USER <> 'FHA\kYarberough' AND SYSTEM_USER <> 'FHA\ljlee' AND SYSTEM_USER <> 'FHA\PHarvey' AND SYSTEM_USER <> 'FHA\BShenosky' AND SYSTEM_USER <> 'FHA\BBrodie' AND SYSTEM_USER <> 'FHA\DRandolph')
Declare @UpdateID as varchar(50)
Set @UpdateID = newid() 

BEGIN

 INSERT [FHA-4].[ECMS_Audit].[dbo].[tblAddress_Audit]
  ([fldAddressOwnerID], [fldUpdateID], [fldAddressTypeCode], [fldAddressMailcode], [fldAddressSequence], 
[fldAddressID], [fldName], [fldLine1], [fldLine2], [fldCity], [fldState], [fldCounty], 
[fldZipcode], [fldWorkFax], [fldWorkPhone], [fldWorkExtension], [fldWorkEMail], [fldHomePhone], 
[fldHomeEMail], [fldContactName], [fldContactPhone], [fldContactFax], [fldContactExtension], [fldEffectiveDate], 
[fldExpirationDate], [fldUpdateTimestamp], [fldUpdateUserID], [fldRelationship], [fldNotes], [fldNCPDPNum], 
[fldMedicaidNum], [fldStoreNum], 
    [ModifiedBySqlUser], [ModifiedByNTUser], [ModifiedDate], [Action] )
 SELECT [fldAddressOwnerID], @UpdateID, [fldAddressTypeCode], [fldAddressMailcode], [fldAddressSequence], 
[fldAddressID], [fldName], [fldLine1], [fldLine2], [fldCity], [fldState], [fldCounty], 
[fldZipcode], [fldWorkFax], [fldWorkPhone], [fldWorkExtension], [fldWorkEMail], [fldHomePhone], 
[fldHomeEMail], [fldContactName], [fldContactPhone], [fldContactFax], [fldContactExtension], [fldEffectiveDate], 
[fldExpirationDate], [fldUpdateTimestamp], [fldUpdateUserID], [fldRelationship], [fldNotes], [fldNCPDPNum], 
[fldMedicaidNum], [fldStoreNum],
   CURRENT_USER, SYSTEM_USER, GETDATE(), 'InitialValues'  FROM deleted


 INSERT [FHA-4].[ECMS_Audit].[dbo].[tblAddress_Audit]
  ([fldAddressOwnerID], [fldUpdateID], [fldAddressTypeCode], [fldAddressMailcode], [fldAddressSequence], 
[fldAddressID], [fldName], [fldLine1], [fldLine2], [fldCity], [fldState], [fldCounty], 
[fldZipcode], [fldWorkFax], [fldWorkPhone], [fldWorkExtension], [fldWorkEMail], [fldHomePhone], 
[fldHomeEMail], [fldContactName], [fldContactPhone], [fldContactFax], [fldContactExtension], [fldEffectiveDate], 
[fldExpirationDate], [fldUpdateTimestamp], [fldUpdateUserID], [fldRelationship], [fldNotes], [fldNCPDPNum], 
[fldMedicaidNum], [fldStoreNum], 
    [ModifiedBySqlUser], [ModifiedByNTUser], [ModifiedDate], [Action] )
 SELECT [fldAddressOwnerID], @UpdateID, [fldAddressTypeCode], [fldAddressMailcode], [fldAddressSequence], 
[fldAddressID], [fldName], [fldLine1], [fldLine2], [fldCity], [fldState], [fldCounty], 
[fldZipcode], [fldWorkFax], [fldWorkPhone], [fldWorkExtension], [fldWorkEMail], [fldHomePhone], 
[fldHomeEMail], [fldContactName], [fldContactPhone], [fldContactFax], [fldContactExtension], [fldEffectiveDate], 
[fldExpirationDate], [fldUpdateTimestamp], [fldUpdateUserID], [fldRelationship], [fldNotes], [fldNCPDPNum], 
[fldMedicaidNum], [fldStoreNum],
   CURRENT_USER, SYSTEM_USER, GETDATE(), 'NewValues'  FROM inserted
 END

COMMIT TRANSACTION
SET XACT_ABORT OFF

Answer 1

嗯，那个触发器对我来说似乎是旧名字。但如果它确实有新名称...嗯...

由于它是分布式事务，您确定链接服务器设置正确吗？

另外，我不想在触发器中使用分布式事务，如果其他服务器关闭，它可能会影响用户更改记录。最好将记录发送到同一服务器上的审核表或发送到运行作业以将记录移动到其他服务器的临时表。