x

Disable triggers in syncro

I need to make a syncronization of to db. The problem is when I insert/update/delete the first db of the send db changes it fires the triggers on the first db again. I dont want to disable triggers of the table because if somebody made a update in the same moment I want that the trigger fire. And also I can't control the trigger code (to made an if statment there) because I can't control all the new triggers. Maybe there is a other option?

more ▼

asked Jan 17, 2010 at 02:06 AM in Default

user-952 (yahoo) gravatar image

user-952 (yahoo)
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort oldest

I suggest you arrange some downtime to do this work in.

This is a prime example of why triggers should be used very, very cautiously and only as a last resort. Triggers are most likely not a good solution for scenarios like yours because they make database maintenance so difficult.

more ▼

answered Mar 01, 2010 at 07:55 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

You dont mention the version of SQL that you are using but there are options like replication and mirroring that could be what you are looking for.

Is the copy for disaster recover, OLAP reporting, different sales area .. etc ?

Do you need it synchronised by transaction, every few minutes, hourly, daily ... ? You may also be able to use Log Shipping if the cycle isnt too short..

more ▼

answered Jan 17, 2010 at 10:03 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

i am using sql 2008. i can use the CDC but the problem is how to move the data and updet or delete in the principlat db without firing triggers
Jan 17, 2010 at 12:22 PM user-952 (yahoo)
(comments are locked)
10|1200 characters needed characters left

Still not sure exactly what you want. but if you want to keep a syncronized copy of your database that you can access for reporting purposes, replication might be what you need. IF you want a copy för availability purposes, you should look into logshipping, mirroring or clustering.

more ▼

answered Jan 18, 2010 at 04:37 AM

Steinar gravatar image

Steinar
1.7k 3 4 6

I realy only want to make and update to de db without fireing the triggers in the table. I cant turn off the triggers and i cant add an if clause to the triggers to prevent his firing.
Jan 18, 2010 at 12:12 PM user-952 (yahoo)

Sorry but the only way I know is this:

USE AdventureWorks; GO DISABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee; GO
Jan 18, 2010 at 12:52 PM Steinar
(comments are locked)
10|1200 characters needed characters left

if you can make use of it, the export/import tool can be used and it does not fire the triggers.

more ▼

answered Apr 12, 2010 at 03:36 PM

Ellen gravatar image

Ellen
1

(comments are locked)
10|1200 characters needed characters left

First, I second dportas in saying that in general triggers should be used sparingly and carefully. I have used them myself and they are sometimes the best solution, but you should very carefully consider whether or not this triggers should be kept.

Often a better answer than a trigger is to deny everyone except the DBA the right to make any changes except through a stored procedure and then make the stored procedure do what you want the trigger to do. This avoids the "hidden" code issue that triggers create amoungst some other complications.

As to your particular situation right now, what you can do is grab a lock on the whole table. That way any other changes will simply queue up while you are working. You can disable the triggers, make your changes, reenable the triggers, and release the locks.

more ▼

answered Apr 26, 2010 at 05:07 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x121

asked: Jan 17, 2010 at 02:06 AM

Seen: 844 times

Last Updated: Jan 17, 2010 at 05:40 AM