April 12, 2005 at 11:10 pm
While Inserting record through trigger on remote server through linked servers, SQL Server gets hanged.
In my program, I am creating a trigger on a table for update, insert and delete. When any of this event is happened, the trigger is fired and one row gets inserted in the table which is on the remote server. To connect the table on the remote server, I am using linked servers.
The triggers gets created successfully, but when any event is occured (update/insert/delete) SQL Server gets hanged.
The trigger is :
CREATE TRIGGER TR_TABLE_INSERT ON TABLE_48
FOR INSERT
AS
set xact_abort on
DECLARE @memberid int
DECLARE @aliasid int
SELECT @memberid=fid,@aliasid=faliasid from inserted
INSERT [remoteserver].[dbname].[dbo].TABLENAME(PID,TABLE_ID,TABLE_NAME,EVENT,MEMBER_ID,COMPOSITE_ID,TIME_STAMP) VALUES(48,2,'TABLE_48','I',@memberid,@aliasid,getdate())
My SQL Server 2000 version is 8.0.0.760.
Can anybody help me on this?
April 14, 2005 at 2:15 am
It probably serves you right as it is not a great idea to do something as slow as a cross server insert from something that really needs quick and tidy code like a trigger - what would happen if the remote server is unavailable?
Really you should either use replication or use a staging table (e.g. your trigger just harvests the unique keys of inserted rows into a staging table and then you have a scheduled procedure to copy rows to the remote server) or of course you could use a combo of harvest and trigger - here you would have a copy of the remote table on the local DB - then the trigger would operate on the local table and that local table would be replicated to the remoteserver - this probably gives you the best of both worlds
James Horsley
Workflow Consulting Limited
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply