I would not like to use TRIGGERS for this, but I think it's the only way.

I have 2 databases with the same structure. What I would like to do is when someone inserts/updates something in this table from Database1, it should insert/update in the same table on database2. should I use triggers on this? I don't like to use triggers because it can mess things up.

  • 1
    For 2008... Log shipping, mirroring, transactional replication, ETL, triggers, and cascading constraints. Those are the options I would look into. I've done something similar and I used transactional replication. (Which is like really mean triggers, but it was resilient.) – Shaulinator Jul 10 '18 at 18:19
  • the problem is, we don't use LOG backup here, as we use all databases in single mode. I used log shipping some years ago and it was really awesome. – Racer SQL Jul 10 '18 at 18:26
  • I think transactional replication or triggers are your best bet then. – Shaulinator Jul 10 '18 at 18:39
  • Yeah I think I'm going to use triggers. Maybe this question should be deleted. I'm trying to create a trigger with both uptade and insert statements. if I made it I will post as an answer, of if someone already knows this, we could use to fit this question and I can change the title to something like ( trigger update insert in same statement ) – Racer SQL Jul 10 '18 at 18:54
  • You can do a trigger with an update and insert in the same. The important piece is handling the order of operations correctly and ensuring you check the DELETED or INSERTED tables and using proper exist checks. You can do an IF logic upsert, I would recommend to look into proper isolation levels as you will run into some problems there. michaeljswart.com/2011/09/… Replication would still be my first choice. – Shaulinator Jul 10 '18 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.