Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KevinKembel
Starting Member
11 Posts |
 Posted - 2008-03-24 : 18:34:02
|
| I have a view that combines several tables, and lastly one column that is determined by a subquery.i.e.CREATE VIEW [View]ASSELECT [TableA].*, [TableB].*, (SELECT TOP 1 [Column] FROM [TableC] ORDER BY [Column2]) AS [SpecialColumn]FROM [TableA] INNER JOIN [TableB] ON [TableA].[ColumnID] = [TableB].[ColumnID]I have made INSTEAD OF UPDATE and INSTEAD OF INSERT triggers for this view that will then allow you to modify [View], and it will handle everything, adding/modifying the data in the three [TableX] tables accordingly.In SQL Management Studio, the following code works fine, and is what is expected...UPDATE [View] SET [SpecialColumn] = 1 WHERE [ColumnID] = @DesiredIDThis will call my instead of trigger and update my special column accordingly exactly how I want it to.Now, the problem lies in the application layer running on top of the database and how it accesses the view. Any call from the application to update [SpecialColumn] returns an OleDB error stating that [SpecialColumn] can not be modified. I assume that sql server is identifying the column as some sort of a derived column that normally wouldn't be able to be updated, and OleDB is restricting the column to just read only. Is there some way to override that in my view to say that the column is not read-only? Is this an OleDB restriction that can't be handled in the database/view itself?I've also tried creating a stored procedure that called the exact same UPDATE [View] SET [SpecialColumn] = @param2 WHERE [ColumnID] = @param1and it also works within management studio, but still gives a similar OleDB error about how the stored procedure attempts to update a derived column that can not be modified.I have a solution where my stored procedure calls the exact same code as the instead of triggers to update the underlying tables of the view manually, however, I'm not asking for a solution like this to my problem, because this works fine, I simply am not happy having to do band-aid workarounds and am wondering if this is a common problem in front-ends and whether it can be handled on the database layer. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-03-24 : 18:53:44
|
quote:
Originally posted by KevinKembel
Now, the problem lies in the application layer running on top of the database and how it accesses the view.
...correct...quote:
Originally posted by KevinKembel
Is there some way to override that in my view to say that the column is not read-only?
...so shouldn't you be looking for a way to override it in your application layer?If the trigger works when you issue an sql insert or update to the table through query analyzer, then the problem is in your application, not the database.Are you using some kind of lame-ass ORM, like NHibernate? If so, you may need to bypass it for this particular portion of the application.e4 d5 xd5 Nf6 |
 |
|
|
|
|
|
|
|
Updateable view