1

I'm working on an assignment for school and it's for MySQL.
I have gotten stuck at the 'Views' topic of my assignment where it asks two very strange questions. The assignment task is to save various different views and then upload a backup of the database to be graded.
Here's the problem though it seems that the things they are asking can't be done in a view.
The only way what they ask is to create the view and then execute a separate query to modify table(view) data.

Here's the odd questions that i can't figure out how to put in a view *Note i have re-worded the questions to avoid copyright issues.

  1. The view must insert a new row into a view(table),

I've been provided with the values they want saved in the view. The way it's written it implies that the INSERT query has to be in the view and executed when you execute the view.

  1. Wright a view that will drop(delete) another view?(is this even possible?)

After hours of searching I've found this can-we-write-update-and-delete-queries-in-views

This says that it can't be done not within the view's code anyway. Have my teachers miss printed what they want or am i missing something? I'm stumped.....

So like the title of this question says, Can you Automatically Modify tables when opening a view?

Community
  • 1
  • 1
KrypticBit
  • 19
  • 3
  • In my opinion this does not make too much sense.. I never seen view to do anything other than to SELECT data.. – JanT Sep 22 '15 at 11:31
  • Thanks for the reply I agree it really doesn't make sense, I've thought of views as the same thing like a 'Window' only showing you specific data from tables. So you think that my teachers haven't asked the right questions? I see what they're asking is better suited to a procedure or a trigger but the Task specifically asked for these things to be saved in a 'view'. I'm going to message the teachers tomorrow and see what they say. Anyone else able to think of a way to accomplish this using a view? – KrypticBit Sep 22 '15 at 11:36
  • when we create views we actually insert data to them, BUT only from the tables stored in the database. So, he means create view table and add data to that table from the database.. could rephrase the second question again in better way! – Kh.Taheri Sep 22 '15 at 11:47
  • Nope i mean exactly what i wrote, I have been provided with the values that have to be added to a specific table. It says i have to create a view that inserts a new row into a table(view) and also a view that drops another view. – KrypticBit Sep 22 '15 at 11:57
  • Views aren't executed. You create a view (with a SELECT), but that includes no execution. Then you select from it, and data is read from the base tables. (Sometimes you can also UPDATE and DELETE from the view, which means the base table is modified.) – jarlh Sep 22 '15 at 12:04
  • You guys are just confirming what i thought, they're expecting me to do something that's not possible :( – KrypticBit Sep 22 '15 at 12:08
  • Does MySQL support instead-of triggers? – jarlh Sep 22 '15 at 12:12
  • I think it does, it was one of the readings i was looking at to solve this problem, But doesn't a trigger require an action other then viewing a table(view) to execute? Never mind it doesn't support it http://stackoverflow.com/questions/25469614/instead-of-triggers-in-mysql it's equivalent (among others) is ironically the INSERT statement :P – KrypticBit Sep 22 '15 at 12:15
  • I fear your rewording of the assignment has distorted its meaning. I can barely even tell where the assignment ends and your commentary begins. It would be helpful to see the original assignment, in full, verbatim. – eggyal Sep 22 '15 at 12:27
  • I cannot imagine that you would have been asked to use a view to actually "drop an existing view". This is definitely not possible. Could it be that the question was directed towards *using* the view in connection with some *data manipulation statements* (`insert/update/delete`) that will cause changes in the contents of your tables behind the view? – Carsten Massmann Sep 22 '15 at 12:37
  • I was afraid re-wording it might do that, I'll try and make it more clear. You may imagine so because it asks, 'make a view that drops another view that's called xxx' (that's 98% word for word)The views part is incomplete without these two being created, how would i even create the second view which deletes another view. They do ask in the next section to take screenshots of the commands used to show the views and there data but nothing about how to add the data, I'll post the response i get from them tomorrow my bet is that they've made a typo or there SQL teacher needs more training. – KrypticBit Sep 22 '15 at 13:26
  • Your assignment is nonsense. There are rules for [updatable views](http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html), but they simply allow you to INSERT/UPDATE/DELETE the data from the underlying tables by executing statements against the view itself. Beyond that, the [MySQL doc](https://dev.mysql.com/doc/refman/5.6/en/create-view.html) explicitly specifies that a view is a SELECT statement. A "view" that INSERTs/UPDATEs/DELETEs would be a stored procedure. – Bacon Bits Sep 22 '15 at 14:04

0 Answers0