INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement
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 only!?
Thanks in advance,

