Error : INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement

• Prakash-205770

  Ten Centuries

  Points: 1079

  More actions

  July 14, 2005 at 6:58 am

  #88927

  Hi all,

  I have a view called Item

  CREATE VIEW ITEM AS

  SELECT * FROM General.DBO.ITEM

  UNION

  SELECT * FROM Specific.DBO.ITEM

  Schema of Item & ItemLog in General & Specific

  Itemcode int, ItemName varchar(50), Rate int, Flag char(1)

  I have a trigger (Instead of Update) on this view :

  CREATE TRIGGER UpdPageItem on Item

  instead of update

  as

  Declare @Itemcode int, @ItemName varchar(50), @Rate int, @Flag char(1)

  Select @Itemcode = Itemcode , @ItemName = ItemName, @Rate = Rate , @Flag = Flag from inserted

  If @flag = 'G'

           Insert General.DBO.ItemLog Values (@Itemcode , @ItemName , @Rate int, @Flag )

  Else

           Insert Specific.DBO.ItemLog Values (@Itemcode , @ItemName , @Rate int, @Flag )

  ---------------------

  Usually this table is updated with only one row!.

  The above trigger works fine if i update with update .... set ....

  If it is updated with the following, it raises the error!

  INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.

   

  update Item set rate = (select rate from ....) where itemcode = @itemcode...

  can u help me!?

  I have to use update ..from onyl!?

  Thanks in advance,

   

  R.Prakash

   

   

   

   

   

  ---

  Kindest Regards,

  R

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  July 14, 2005 at 7:06 am

  #574349

  Please do not cross-post, we monitor all boards.

  Finish the thread here : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=201010