Triggers and Linked Servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Triggers and Linked Servers

Author

Topic

otto
Starting Member

21 Posts

Posted - 2003-01-11 : 15:31:15

I need a little help with Triggers.

I have ServerA and ServerB setup as linked servers. When someone inserts, updates, or deletes a record on ServerA, I want a trigger to fire and insert, update, or delete the record on ServerB.

I have the ServerA linked to ServerB so I can execute queries against it. At this point, I'm only having three problems:

1- When ServerB is down, I want ServerA to still write the insert, update, or delete
2- I can't figure out how to update ServerB when ServerA's record get changed
3- Is there a way to create a insert, update, and delete trigger with different code for insert/update/delete?

Am I going about this the wrong way? Also, here's a copy of my update trigger.

---------------------------
CREATE TRIGGER tr_Update ON [dbo].[MyTable]
FOR UPDATE
AS

SET XACT_ABORT ON

DECLARE @Field1 varchar(10), @pk int

SELECT @Field1 = Field1 FROM UPDATED

IF @@RowCount = 0 RETURN
-- UPDATE <Server>.<DatabaseName>.dbo.MyTable
UPDATE Server.DatabaseName.dbo.MyTable
SET
Field1 = @Field1
WHERE pk = @pk

SET XACT_ABORT OFF

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-12 : 09:40:11

Why not use replication for this? This kind of thing is exactly what it was intended for.

otto
Starting Member

21 Posts

Posted - 2003-01-12 : 17:31:05

quote:

Why not use replication for this? This kind of thing is exactly what it was intended for.

Thanks Rob, I'll look into replication.

Could you explain why my update trigger isn't working? Am I not selecting the data properly? Could you also address if this could be done with one trigger or will I need three?

Otto

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-12 : 18:41:08

For one thing:

UPDATE Server.DatabaseName.dbo.MyTable
SET
Field1 = @Field1
WHERE pk = @pk

The WHERE clause is meaningless here because the value of @pk is never set within the trigger.

For another:

SELECT @Field1 = Field1 FROM UPDATED

There is no UPDATED pseudo-table in a trigger. You would get the new value from the inserted pseudo-table (the old values would come from the deleted pseudo-table)

Also, your trigger is written in such a way that it will only work correctly for single-row updates. If you row a multi-row update on it, you will not be able to process the entire set correctly.

There are examples of triggers in Books Online that show the proper way to get values from inserted and also show how to handle all updated rows, not just one.

Lastly, if the linked server is down the entire trigger would fail, as written, and might cause the entire update to roll back. If you can't use replication, I would suggest putting the updated rows into another table (an audit table) and set up a scheduled job to process the audit table and synchronize with the linked server. That way the data will accumulate until the linked server is available, and the job can clear out the audit table when synchronization is complete. However, replication is really you're best bet as it will handle everything described here with no intervention from you, once you set up replication and schedule it.

otto
Starting Member

21 Posts

Posted - 2003-01-14 : 19:15:34

Rob,

Thanks for all of your replies. One last question and I think I've beaten this subject to death.

How do I process multiple records if they update or add bulk items? I read and re-read Books Online and couldn't find how to do it.

Thanks,

Otto

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-14 : 19:53:41

Is this post and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22984 the same?

I think that you REALLY need to use replication / log shipping for this. I believe they will solve all of the problems you have. I bet that replication will be more reliable and less problematic than your current home grown solution.

Give it a try!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources