Solved

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

Posted on 2011-09-16
5
198 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
  • 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now