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

Question

Post reply

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 7:00 am

#164321

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chiranjib Chowdhury SSC Veteran Points: 216 More actions · Answer 1

Are u trying to update with this statement?: update Item set rate = (select rate from ....) where itemcode = @itemcode...

If so then what i think is the "select rate from ...." statement within update can return number of rates 4 a single itemcode and 4 a single cell in item table rate column. probably that is the reason it is giving error. pls check it.