SQL 2005 Trigger Not Firing Reliably

Question

I'm currently using Microsoft Sync Framework and everything is working fine except the SQL triggers which don't appear to fire sometimes. Here is the trigger code generated by the framework. It works 90% of the time but occasionally the CreationDate is NULL after an insert. Could there be some date issue that I'm overlooking?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyTable_InsertTrigger] 
    ON [dbo].[MyTable] 
    AFTER INSERT 
AS 
BEGIN 
    SET NOCOUNT ON 
    UPDATE [dbo].[MyTable] 
    SET [CreationDate] = GETUTCDATE() 
    FROM inserted 
    WHERE inserted.[DataDate] = [dbo].[MyTable].[DataDate] 
END;

Answer 1

There's nothing obviously wrong with the trigger - but the fact you're getting these errors suggests that in certain cases, the join between [inserted].[DataDate] and [dbo].[MyTable].[DataDate] is failing, or that the trigger is disabled when the insert takes place.

Is this a simplified example to illustrate your problem? If not, you don't really need a trigger to set CreationDate - why not use a default value on the column?

ALTER TABLE dbo.MyTable ADD CONSTRAINT Df_MyTable_CreationDate DEFAULT GETUTCDATE() FOR CreationDate

(Don't forget to drop the trigger)

Answer 2

nested triggers and or recursive triggers settings may prevent triggers form firing. Also triggers may be disabled or even dropped/recreated.

Answer 3

Hi,

i suggest a little enhancement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyTable_InsertTrigger] 
    ON [dbo].[MyTable] 
    AFTER INSERT 
AS 
BEGIN 
    SET NOCOUNT ON 
    UPDATE [dbo].[MyTable] 
    SET [CreationDate] = GETUTCDATE() 
    FROM inserted, MyTable 
    WHERE inserted.[DataDate] = [dbo].[MyTable].[DataDate] 
END;

This is because you can have more than one row in the virtual table INSERTED and therefor it has to be joined to your table [MyTable] correctly; and use a unique key to join, so have a look at a better choice than [DataDate] (assuming that this is of type [datetime]).

Good Luck and peace

Ice