• Uncategorized

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>

and

ENABLE TRIGGER <TRIGGERNAME> ON <TABLENAME>

Command syntax to disable all triggers of a table.

ALTER TABLE <TableName> DISABLE TRIGGER ALL

Deepak Kamboj

Deepak Kamboj is a Solution Architect and Technology Enthusiast, located at Redmond, WA, having 14+ years of hands on experience in the IT industry.

You may also like...