Solved

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

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Question 5 36
Count with a subquery showing details 10 41
SQL Help 27 40
Why i am getting a star, SSMS does not show me any error. Division Error 5 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

806 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