?
Solved

Setup Triggers or simliar to insert record into a table in different database

Posted on 2011-09-16
5
Medium Priority
?
212 Views
Last Modified: 2012-05-12
Hello,

I have 2 databases existing on 2 different servers.
One is SQL 2008 and other is SQL 2000

I would like to achieve following:

1. when a new record gets inserted into a table on 2008 DB, there would be a setup/code that automatically senses that and gets all data or part of that data and insterts it as new record into 2000 DB table.

Similar, when an exisiting record in 2008 is updated or deleted, the matching record in 2000 DB would also get updated or deleted too


What SQL feature/tool you recommend to me to use?
I heard about triggers and similar, but not sure how they work, where to set them up, what code to use, etc

If you can help me with an advise and maybe some sample code for some script or similar that has to be used, that would be great.

Thank you in advance.
0
Comment
Question by:Terrace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36551068
What you are suggesting can be done with a trigger, but it's probably not a good idea.  You would have to create linked server on the 2008 server that points to the 2000 server.  You could create a trigger that inserts data via that linked server.

Here is why you probably shouldn't go that route.  By setting up the trigger, any inserts into the 2008 table are going to depend on the 2000 server.  If the trigger cannot write to the 2000 server for any reason, like it being unavailable, you won't be able to write data to the 2008 table.  It will roll the entire transaction back.  Plus, there are performance implications.  You will most likely be slowing the insert data process down substantially.  

So, what are some other alternatives?  If this is coming from an application, you could make separate connections to each database and write the data.  If the data doesn't have to be real time in the 200 server, you could create a job that runs every few minutes to get the data changes and write them to the 2000 server.  You could probably even set up database mirroring between those two tables.

Greg

0
 

Author Comment

by:Terrace
ID: 36551222
Thank you Greg,

Unfortunately, I don't control the application code, so I can't make separate connection to each DB.
I have access/control of database only, so I must act on DB.

Data on 2000 server may be inserted or updated with slight delay (few mins should not be problem)

I know how to setup scheduled jobs to run one or more scripts, but I am not sure how to write an actual script that will be able to sense changes that happened in 2008 db, and then make those same changes in 2000 db.

Can you maybe show me an example or send me a link where I can find some info about that?

Thanks
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36551513
The issue is how are you going to tell what data has changed between runs of the job.  You could create a new table in the SQL Server 2008 server to hold changed data for this table.  It would probably look identically to the source table, but with an extra column to identify whether the data is being inserted, updated or deleted.  You could create a trigger that insert the changes into the new table.  The job would then run a script that inserts the inserts, updates the updates and deletes the deletes.  In the attached script, my source table is Test and my changes table is Temp.  I hope this makes sense.  It's not a simple task.

Greg


CREATE TABLE Test(ID INT, Value VARCHAR(50))
GO

CREATE TABLE Temp(ID INT, Value VARCHAR(50), Operation CHAR(1))
GO

CREATE TRIGGER T_Test ON Test FOR INSERT, UPDATE, DELETE
AS

DECLARE @DeletedID INT, @InsertedID INT

SELECT @DeletedID = deleted.ID FROM deleted
SELECT @InsertedID = inserted.ID FROM inserted

IF @DeletedID IS NULL
	INSERT INTO Temp 
	SELECT inserted.ID, inserted.Value, 'I'
	FROM inserted
	
ELSE IF @InsertedID IS NULL
	INSERT INTO Temp
	SELECT deleted.ID, deleted.Value, 'D'
	FROM deleted
	
ELSE 
	INSERT INTO Temp
	SELECT inserted.ID, inserted.Value, 'U'
	FROM inserted
	
GO

Open in new window

0
 

Author Comment

by:Terrace
ID: 36551732
Thanks Craig,

I'll check it out now.

How to I setup the trigger in SQL Enterprise Manager, where?

Your code will go into a stored procedure, I assume.

Then from where I call that stored procedure?

Thanks for your time and all the help.

Also, if I go with the route #1 (linked server), I assume I would need to take same approach regarding setting up triggers, except I don't need to have this additional table that tracks/stores what was inserted, updated, deleted.

I may risk and go with the option #1.

If receiving DB (SQL 2000) ever goes down, we can always go into 2008 and disable linked server feature, until 2000 is up and running?

Let me know what you think.
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36551787
The script that I gave you is the script to create the trigger and should be run in SQL Server Management Studio(SSMS).  In SQL Server 2005/2008, SSMS replaces Enterprise Manager and QueryAnalyzer.  It combines them into an interface similar to Visual Studio.  It's not a stored procedure script.

You would disable the trigger, not the linked server.

Greg



0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question