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.
| Author |
Topic |
|
sachina
Starting Member
1 Post |
 Posted - 2005-04-12 : 12:05:49
|
| 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. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-12 : 13:46:48
|
| I have 3 suggestions. 1) A trigger should never assume that only 1 record is being Inserted,Updated, or Deleted. So for your Insert trigger, you should do this instead:Insert TableName (PID,TABLE_ID,TABLE_NAME,EVENT,MEMBER_ID,COMPOSITE_ID,TIME_STAMP)Select 48, 2 , 'TABLE_48', 'I', i.fid, i.faliasid, getdate() from inserted i2)I wouldn't tie trigger functionality to a Remote server call (RPC). Instead, have the trigger write to a local "staging" table. Then have a continious or repeating Job make the RPCs based on the staging table. That way any connection or remote server issues won't affect writing to your local table.3)I would have stored procedures on the remote server that is called rather than direct table manipulation from the RPC.EDIT:btw,Would replication serve your purpose better?Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
While Inserting record through trigger on remote s