January 4, 2006 at 9:02 am
i have a view declared as:
create view foo as select *, (select top 1 somefield from other) as topfield from bar
if i want to update this view i get the following message:
update foo set anotherfield = 'X'
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'FOO' is not updatable because a column of the derived table is derived or constant.
according to the help this is correct and i have to use an INSTEAD OF UPDATE trigger.
CREATE TRIGGER FOO_IOT ON FOO INSTEAD OF UPDATE AS <X>
can anybody help me with the <X> part, i know i have the deleted and inserted resultsets but how do i make the SET Clause, i dont want any dynamic sql in the trigger and i also dont want to make all possible permutations of all fields using UPDATED(anotherfield) because i dont know beforehand what fields are updated.
TIA
Chris Endhoven
January 5, 2006 at 3:41 am
It seems like the update may involve changes to more than one basetable? ie columns both the 'main' table and also in the basetable from where the derived column comes.
Perhaps it's worth considering calling a stored procedure instead of updating the view directly?
The proc would know which parameter goes into which table and could then perform the # of updates needed without involving triggers.
It does however require that the update call from the client being changed slightly.
An idea perhaps?
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply