Database Reference
In-Depth Information
Now let's continue and perform some inserts, updates, and deletes using
the views USARTISTS and CONSTRAINT_USARTISTS created earlier
in the chapter. Let's say you have a new artist who will be using your studio
to record her latest song. The following command will insert a row into the
ARTIST table using the CONSTRAINT_USARTISTS view:
INSERT INTO CONSTRAINT_USARTISTS VALUES
(ARTIST_ID_SEQ.NEXTVAL, 'Judy Madrid', 'Madison'
, 'WI','53887', 'USA');
When inserting or updating rows using a constraint view, like the
CONSTRAINT_USARTISTS view, the new or modified row must still fit
within the view. In this case, because the WHERE clause of the view is
WHERE COUNTRY='USA', that means the row's COUNTRY column
must be USA.
Here is another important point about inserting data with a view: You
can only insert values into the columns listed in the view. All other columns
will be NULL or assigned a default value. In this example, the following
columns will be NULL in the newly inserted row: STREET, POBOX,
EMAIL, and INSTRUMENTS.
Now try updating the row just inserted using the USARTISTS view:
UPDATE USARTISTS SET ZIP = '53200'
WHERE NAME = 'Judy Madrid';
Finally, delete the row using the CONSTRAINT_USARTISTS view:
DELETE FROM CONSTRAINT_USARTISTS
WHERE NAME = 'Judy Madrid';
Views that have columns made up of functions or other expressions can
still be used to modify the base table. This view can illustrate that point:
CREATE OR REPLACE VIEW SONG_VIEW AS
SELECT SONG_ID, ARTIST_ID, TITLE, RECORDING_DATE
, SUBSTR(PLAYING_TIME,1,1) MINUTES
, SUBSTR(PLAYING_TIME,3) SECONDS
FROM SONG;
 
Search WWH ::




Custom Search