Error : INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement
-
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
-
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.
-
I'm having a similar issue, has there been a resolution for this?
-
Hi,
From the SQL Server Books online, it is found that it is not possible to use UPDATE FROM statement on a view that has INSTEAD OF UPDATE trigger.
There is no way to do this. We should change the code.
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