SQL Azure Data Sync not possible when a trigger is set

Question

I try to use SQL Azure Data Sync to sync between SQL-Azure-DB and on-premise SQL-Server 2008R2.

I have to sync only one table with now only 90 rows. The table on the Azure-side has a trigger set, but the table on my local sql-server doesn't have this trigger (because apart from the shared one table, the two databases have nothing in common).

When I try to sync it fails with this error:

Sync failed with the exception "GetStatus failed with exception:
Sync worker failed, checked by GetStatus method.

Failure details:
An unexpected error occurred when applying batch file C:\Resources\directory\61e9d741c80a47b4ae195f835e62fcda.NTierSyncServiceWorkerRole.LS1\DSS_syncjobmxd24sznwfq5idekfaopaery\a0e8b11a-a08c-4081-b929-e3f80b70f045.batch.
See the inner exception for more details.

Inner exception:
Failed to execute the command 'BulkInsertCommand' for table 'dbo.tblUser';
the transaction was rolled back. Ensure that the command syntax is correct.

Inner exception:
SqlException Error Code: -2146232060 - SqlError Number:512,
Message: Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >=
or when the subquery is used as an expression.

SqlError Number:3621,
Message: The statement has been terminated. 
For more information, provide tracing id ‘bb8e3787-27c1-4b7e-9a26-6db2ff6724d3’ to customer support.

When I disable my trigger, the sync works!

My trigger:

    CREATE TRIGGER [dbo].[UpdateUsersTable]
    ON [dbo].[tblUser]
    AFTER INSERT AS BEGIN SET NOCOUNT ON;

    INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])
    VALUES ((select [userID] from inserted), (select [country] from inserted))
    END

As an approach I thought maybe it's because my trigger and the sync-trigger start in the wrong order, so I tried:

    exec sp_settriggerorder @triggername = 'tblUser_dss_insert_trigger',
                        @order = 'first',
                        @stmttype = 'insert',
                        @namespace = null

    exec sp_settriggerorder @triggername = 'UpdateUsersTable',
                    @order = 'last',
                    @stmttype = 'insert',
                    @namespace = null
    go

But that didn't change anything. Still the same error.

Do I have any chance to sync that table WITH my trigger enabled?

Answer 1

In your Trigger you are using INSERT which is causing this problem.

Please create separate triggers for INSERT, UPDATE and DELETE. System do need to compare the row counts between inserted and deleted table to determine which action fires the trigger. Having separate triggers for INSERT/DELETE should solve this problem.

A similar discussion shows exact same problem here

More Info

Your trigger has a bug, and will fail for any multi-row insert. It should be something like:

CREATE TRIGGER [dbo].[UpdateUsersTable]
ON [dbo].[tblUser]
AFTER INSERT AS BEGIN SET NOCOUNT ON;

INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])
 select [userID], [country] from inserted
END