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 only!?

Thanks in advance,

[1316 byte] By [PrakashDgl] at [2008-2-13]
# 1
Hi

I do not understand exactly what you mean, but I think I can maybe help you. Could you please post the full code of your trigger that isn't working?

Teggno

Teggno at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
is that your update fails if more than 1 row is updated with your update clause.
brokenrulz at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3
You are not handling the trigger as a set base operation. You are doing it as a record base which is wrong. When the update trigger fired, as a result of an update operation, there might be more than one record affected by the update operation. And hence the inserted table will contains more than one record.

And this statement will definately not handling this kind of situation.

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

KHTan at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4

Hi

Probably , what you are looking at is something like this, This trigger will handle set update:

CREATE TRIGGER UpdPageItem on Item
instead of update
as


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

-- If @flag = 'G' insert into General.DBO.ItemLog

Insert General.DBO.ItemLog (Itemcode , ItemName , Rate )

SELECT Itemcode , ItemName , Rate

FROM inserted

WHERE @flag = 'G'

-- AND NOT EXISTS (SELECT ...)

--you might want to check here that your values are not in General table already

-- Else insert into Specific

Insert Specific.DBO.ItemLog (Itemcode , ItemName , Rate )

SELECT Itemcode , ItemName , Rate

FROM inserted

WHERE @flag <> 'G'

NB2006 at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server

Site Classified