Solved

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

Posted on 2011-09-16
5
197 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

19 Experts available now in Live!

Get 1:1 Help Now