Any reasons trigger AFTER INSERT would not fire?

Posted on

Question :

Context:

We’re using microsoft sync framework in our project.

Sync creates triggers AFTER INSERT, DELETE, UPDATE triggers for tables that are provisioned to sync.
the framework also creates tracking tables, that get updated by these triggers, so it can successfully keep database in sync with each other, when a sync is performed.

The issue:

We have a customer, whom is managing to insert data into one of these tables that contain these triggers, but the information isn’t making it into the corresponding tracking table via the trigger.

The Question:

Are there any ways to insert data into a table, that would circumvent the firing of an AFTER INSERT trigger?

Trigger Example:

CREATE TRIGGER [dbo].[Claims_insert_trigger] 
ON [dbo].[Claims] 
FOR INSERT 
AS
BEGIN
SET NOCOUNT ON;
UPDATE [side] 
SET [sync_row_is_tombstone] = 0
    , [local_update_peer_key] = 0
    , [restore_timestamp] = NULL
    , [update_scope_local_id] = NULL
    , [last_change_datetime] = GETDATE() 
FROM [Claims_tracking] [side] 
    JOIN INSERTED AS [i] ON [side].[ClaimID] = [i].[ClaimID];

INSERT INTO [Claims_tracking] 
(
    [i].[ClaimID]
    , [create_scope_local_id]
    , [local_create_peer_key]
    , [local_create_peer_timestamp]
    , [update_scope_local_id]
    , [local_update_peer_key]
    , [sync_row_is_tombstone]
    , [last_change_datetime]
    , [restore_timestamp]
)
SELECT [i].[ClaimID]
    , NULL
    , 0
    , @@DBTS+1
    , NULL
    , 0
    , 0
    , GETDATE()
    , NULL 
FROM INSERTED AS [i] 
    LEFT JOIN [Claims_tracking] [side] ON [side].[ClaimID] = [i].[ClaimID] 
WHERE [side].[ClaimID] IS NULL;
SET NOCOUNT OFF;
END

Answer :

It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.

Leave a Reply

Your email address will not be published.