Solved

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

Posted on 2011-09-16
5
209 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 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