SQL Server 2005 triggered audit tables moved to SQL Server 2008, now trigger does not respond when trying to insert row into audit table

Question

We began with SQL Server 2005 database and tables. [UPDATE, INSERT and DELETE] in this case we were using the UPDATE trigger(s) to insert rows into audit tbl(s) when application (VB6) data table is modified. We moved the audit tables to SQL Server 2008. The only change in the trigger statement(s) (on the SQL Server 2005) we modified the original ([FHA-4]) to the new (SQL Server 2008 [FHA-DMZ-CL1SQL]) server name.

When the trigger is activated the hour glass stays on until a sql timeout message appears and the application aborts. When checking the audit tables nothing new is added so the insert did not work.

Here is the actual trigger statement for the table:

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

well that trigger appears to have the old name to me. But if it really does have the new name...hmmm...

Since it is a distributed transaction are you sure you have the linked server set up correctly?

Also I'd prefer not to use a distributed transaction in a trigger, it could affect users being able to change records if the other server is down. MIght be better to send the records to an audit table on the same server or to a staging table that runs a job to move the records to the other server.