1

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
  • Are you sure that's the only trigger on the table? Are you sure it's enabled? Are you sure the caller has rights on both tables? Are you sure the initial insert has succeeded? – Aaron Bertrand Mar 5 '15 at 19:53
  • there are 3 triggers, update, delete, insert, Possitive there enabled, and the database is being connected via localdb which by my experience and understanding only has what is equivalent to the SA account on normal SQL server. And I'm sure the initial insert has succeeded, the records exist in the table that contains the trigger, but do not exist in the tracking table the trigger is supposed to insert the tracking record into. – WillFM Mar 5 '15 at 22:40
1

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.

  • I'll have to check some code to see, if a transaction is even being used in this case, but I know they wouldn't be disabling the trigger. I was wondering more if, maybe a bulk, or multi insert of some sort, would bypass a trigger, (kinda like truncate table vs delete from table) – WillFM Mar 5 '15 at 19:29
  • You don't have to start a transaction manually. If an error occurs Inside a trigger, SQL does so by itself. social.technet.microsoft.com/wiki/contents/articles/… – Danielle Paquette-Harvey Mar 5 '15 at 19:34
  • And yes, if you have a multi insert, your trigger must be built to deal with it the right way, or else, you will lose some data. (ex: only the first row will be processed by the trigger if it is not programmed correctly) – Danielle Paquette-Harvey Mar 5 '15 at 19:35
  • The trigger looks like it should handle multiple insert's, i've added an example just so other eyes can look over it. Again, Microsoft Sync Framework generates these triggers automatically, we did not write them. – WillFM Mar 5 '15 at 19:52
  • 1
    In this case, thats not a viable solution, our product uses SQL Server LocalDB, along side SQL Azure (not profilable), with microsoft sync, keeping them synchronized. So a majority of our client just have the SQL Server localDB distributable, with our software, And the issue is intermittent, and the code that inserts the data, into the table is coming from a vary old ADO driver. I may be going the profiling route on my development environment, once we have a surefire use case to re-create the issue. I do appreciate all the input though! – WillFM Mar 5 '15 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.