|
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?
(comments are locked)
|
|
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.
(comments are locked)
|
|
if you can make use of it, the export/import tool can be used and it does not fire the triggers.
(comments are locked)
|
|
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.. 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 '10 at 12:22 PM
user-952 (yahoo)
(comments are locked)
|
|
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. 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 '10 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 '10 at 12:52 PM
Steinar
(comments are locked)
|
|
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.
(comments are locked)
|
