SQL Server Tip: Disable and enable triggers on synchronization activities
Today we will delve on how to disable triggers before a synchronization and how to renable them after the manual synchronization is complete.
Manual Synchronization takes place for online/offline applications wherein an offline/mobile version of database communicates with a second instance of the database that resides on the server and vice versa. For synchronization, there needs to be greater control on how the data is synchronized. In such a scenario, there would be triggers on various tables which lead to further insertions on other tables and these re-insertions are to be avoided. To avoid re-insertion, disable the trigger before synchronization and enable the trigger after the synchronization is complete.
The syntax to disable and enable trigger is given below:
DISABLE TRIGGER <TRIGGERNAME> ON <TABLENAME>
ENABLE TRIGGER <TRIGGERNAME> ON <TABLENAME>
Command syntax to disable all triggers of a table.
ALTER TABLE <TableName> DISABLE TRIGGER ALL