| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-05 : 09:31:23
|
| Summer writes "Hi!I have view where's two tables (SQL server 7)SELECT product.key, product.name, product.id, product.type, property.nameFROM product LEFT OUTER JOIN property ON product.key = property.keyCan I delete records from product table with this view. There's all rows from product table, but not all columns. If not, Do I need this Instead of trigger?How that works?regardsSummer" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-05 : 09:51:18
|
| Have you tried deleting rows from the view?setBasedIsTheTruepath<O> |
 |
|
|
Summer
Starting Member
7 Posts |
Posted - 2002-06-06 : 01:04:39
|
| Sorry, maybe I should of write How can I delete rows!Yes, I have try it in my program and query analyzer!Query Analyzer :View or function 'name' is not updatable because the modification affects multiple base tables. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-06 : 01:22:54
|
| Summer you are on the right track , you need to write a instead of trigger to update a view based on multiple tables. Have a look , this is from BOLIf a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that: The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified. Note Partitioned views using the UNION ALL operator can be updatable.No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference. EDITED: Sorry, for the inconvenienceHTH--------------------------------------------------------------Edited by - Nazim on 06/10/2002 09:17:13 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-06 : 02:26:11
|
| SQL Server 7 does NOT support INSTEAD OF triggersDavidM"SQL-3 is an abomination.." |
|
|
|
Summer
Starting Member
7 Posts |
Posted - 2002-06-06 : 07:47:50
|
| What If I'd have SQL server 2000, Does that support?I can get that!What use views then have! If you can only update single table based views, that's almoust same than table! I noticed one new thing about view!If I have Order by clause in that view, you must specifid TOPand you can't update that at all?Summer |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-06 : 08:33:32
|
| what use views.....???.....2 reasons amongst many....1. you can restrict columns in a table from being seen by unnecessary people....standard example would be payroll details on a staff member table.....important for those needing access to the staff member table to be able to see name, location, grade....but only authorised payroll people would need to see salary.therefore 2 views on staff table....1 with name, location, gradeand2 with name, location, grade and salary...2. views can display information from multiple tables...thus hiding the complexity of linking the underlying data from the end-user....regarding the problems updating them.....think about if for a while...it makes sense...if you create a view which is a join on 2 tables....and wish to update the information contained therein....how do you issue an UPDATE statement on 2 tables at the same time....go look at the format of the UPDATE statement and you'll see it can't be done.a view is just a nice packaged way of displaying the results of a select statement and locking it down as a callable result set....and they do have their uses...it's just a matter of recognising the limitations on using them...... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-06 : 08:45:55
|
quote:
What use views then have! If you can only update single table based views, that's almoust same than table! I noticed one new thing about view!If I have Order by clause in that view, you must specifid TOPand you can't update that at all?
Summer-I'm gonna rant a little here, but it's for your own good. Slow down a little and think about what you're trying to do. You need to read up on views and SQL Server more to find out what they CAN do, not what they CAN'T do. As Andrew has shown, there are several instances where views are very useful.Think about it: if views really were useless, why would they be available in database products?The other reason you need to slow down is because you're jumping into some advanced features without fully understanding the process you're developing. You need to understand the side effects of joins, views, and triggers because you can easily paint yourself into a corner and it can cause various problems for you later on. I can tell you now, if you did understand this fully, you wouldn't have a question about why you can't update or delete from a view that uses a LEFT JOIN (and an ORDER BY)One last thing, really small, but you don't have to put an exclamation point (!) at the end of each sentence, it's pretty annoying if you do.Edited by - robvolk on 06/06/2002 08:46:33 |
|
|
|
Summer
Starting Member
7 Posts |
Posted - 2002-06-07 : 01:13:04
|
| Here is quite sensivite people, with no sence of humourAnd you can think that people that have never use views before, just ask first can I update views and other like that...I've seen here pretty much more simplier questions, so I tought that you can ask something even you are not guru yourself!!!!I was updating one table, not both.Summer |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-10 : 08:37:06
|
| Summer, you say you want to update 1 table...not both...the trouble for SQL is that it doesn't know that!!...because you are using a view which reads from 2 tables.... you can update 1 table....if you use a view which only references 1 table... re asking questions....no problems there...as long as you listen to the answers given...advice that comes from a 4000+ post person should usually be taken on board...or at least examined carefully enough for you to be sure it's worth discarding....after all you're the one with the original problem...it won't affect our paycheck if your application works or not... re humour...humour disappears after post #100...on "dynamic sql", "dateformats", "homework questions"...amongst others....including doing our regular day jobs.......but it re-appears after "thanks", "gracias", "go raibh maith agat"...and other appropriate phrases....if you're trying to be funny....give us a hint...remember we can't see your face....so we don't know you're smiling!!!....that's what the smilies are for!!...............one man's humour is anothers cutting sarcasm. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 08:42:39
|
quote:
advice that comes from a 4000+ post person should usually be taken on board...or at least examined carefully enough for you to be sure it's worth discarding
Andrew's 100% correct, I've gotten much better at hiding obvious errors and fundamentally dumb approaches... |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-06-10 : 09:08:49
|
| Can someone edit the [code] tags out of Nazim's post so it's possible to read this thread?Edit: Thanks.Edited by - Arnold Fribble on 06/10/2002 11:31:04 |
|
|
|
Summer
Starting Member
7 Posts |
Posted - 2002-06-11 : 06:54:57
|
quote:
you to be sure it's worth discarding....
I wasn't discarding advices, sorry if you get that impression?quote:
if you did understand this fully, you wouldn't have a question about why you can't updateor delete from a view
Reason why ask this at the first place is this text which I found from manual."The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed..."So that's why I ask so stupid questions. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-11 : 07:08:22
|
| Summer,Sorry, new to this thread....Which manual did you find this in?PeaceRick |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-11 : 07:18:26
|
| Summer, let's call time on the arguement side of this....we all have misunderstood each other...the english language written (or spoken) can be unforgiving at times....we also all should have used quotes around the "Instead Of" phrase....so that it was obvious people were talking about the SQL keyword and not as part of an ordinary english sentance....the manual statement....really was highlighting the "upside" of "Instead Of" triggers....and not the "downside" of "views"....sometimes in covering both angles, the primary objective of the piece gets lost. |
|
|
|
Summer
Starting Member
7 Posts |
Posted - 2002-06-12 : 03:22:37
|
| Rick,It's SQL server 2000, Books OnlineregardsSummer |
|
|
|
|
Updatable Views -Instead of trigger