dbo Trigger

[zeusToronto]

Hi, I am trying to add a trigger on insert event on a table in SQL_Express. ALTER trigger [dbo].[TR_Insert_New_Pair] on [dbo].[Vendors] FOR INSERT AS DECLARE @ACCPACID varchar(12), @VNAME varchar(60) SELECT @ACCPACID = (SELECT ACCPACVENDORID from inserted), @VNAME = ( SELECT VENDNAME FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID)) ) UPDATE dbo.Vendors SET VENDNAME = @VNAME WHERE LTRIM(RTRIM(ACCPACVENDORID)) = ltrim(rtrim(@ACCPACID)) --This part works great (I've tested) SELECT VENDNAME FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID)) Is there something I am not seeing ???? Thanks Kirill

[Ashfield]

zeusToronto wrote:

Is there something I am not seeing ????

I'm not seeing your question. What the problem and error message?

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[zeusToronto]

I am sorry, when Insert happens trigger does not seem to update the line. This trigger has other parts to it which seem to work just fine.

[Ashfield]

Have you checked that the value from the linked server is being populated? Have you tried the simple test of hard coding a value into @Vname and seeing if that works? I have a sneaky feeling that linked servers don't work from triggers, but I'm not certain.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[zeusToronto]

Ashfield this is exactly how I've tested everything. Values do come from linked server, and when I hard code the value trigger seem to update it just fine. I even went as far as changing the trigger to update from the local table (LOCAL SQL Table) and it works. The only issue I am having is when update happens from a linked server.

[Ashfield]

zeusToronto wrote:

Values do come from linked server

Is that tested from within the trgger or just as a piece of sql? Based on your reply, if you hard code it or get the values from a local table it works, the only time it doesn't is when you get the values from a linked server in the trigger. The only cnclusion you can draw from this is that the linked server fetch is not working in the trigger.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP