Creating a single trigger for Insert/Update/Delete in SQL with Two Database

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I am using SQL Server 2012.

I Need to create a Single Trigger From First_DataBase TableA to Second_DataBase TableB

When ever any modification done on TableA it'll affect the TableB

In this concept i am using Two DataBase

Whenever a row is inserted or updated or deleted in Table A, it should be affect the Table B.

Same Columns Name for TableA and TableB

How to handle this from a single trigger?

Posted 18-Jun-15 7:00am

Member 11775628

Add a Solution

Comments

virusstorm 18-Jun-15 13:12pm

I need to throw out a disclaimer first. This is design will lead to a great deal of trouble long term. First recommendation is not do this at the database level, but in the business layer of your application. If you can't do that, I would use a stored procedure. Lastly, and only as a last resort, I would use a trigger.

With the disclaimer out of the way, I need to know if these databases are on the same server and will always live on the same server.

Member 11775628 18-Jun-15 13:22pm

In this concept DataBaseA having 40 tables, Just i am creating a New Database as DataBaseB, I am going to copy all the 40 tables from DataBaseA to DataBaseB and i am renaming the 40 Tables, After that i am going to Use Trigger Concept for INSERT\UPDATE\DELETE, and Whenever a row is inserted or updated or deleted in Table A (DataBaseA) , it should be affect the Table B(DataBaseB) ......

ZurdoDev 18-Jun-15 19:35pm

1. Reply to comment so that the user is notified.
2. See Ben's comment. Use replication.

virusstorm 18-Jun-15 21:08pm

Agreed with Ben. You should be using replication for this. Doing this through triggers is going spell lots of trouble for you. Take a look at this MSDN article and let us if you have any questions.

https://msdn.microsoft.com/en-us/library/ms151198%28v=sql.120%29.aspx

Ben J. Boyle 18-Jun-15 14:31pm

Isn't this what replication was designed for? Why reinvent the wheel?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sreekanth Mothukuru · Answer 1 · 2015-06-19T01:07:00

Solution 1

Why don't you use data replication concept in this regard. Why because maintaining two databases in sync manually is not a good idea.

If you still want to do it through triggers, write a common stored procedure to handle the task in all three triggers.

Posted 19-Jun-15 1:07am

Sreekanth Mothukuru