Objectives
- Try out how to implement editability for not editable views.
- Practice creation of triggers.
Motivation
Today we continue working with triggers, which we started last week after exam.
In this week you can write repair test exam, therefore if you need you, use it. I want you to know that only the last attempt will be taken into account, therefore if you will write repair test exam you will get points from it.
Instructions
Step 1
During 7.week lab you have been tasked to get list of artists together with composers without any duplicates, ordered alphabetically. Next block of code creates view, which will get such list:
CREATE OR REPLACE VIEW allArtists AS
SELECT name
FROM artist
UNION
SELECT composer
FROM track
WHERE composer IS NOT NULL
ORDER BY name;
Task 1.1
Create this view in your database.
Because this view uses UNION and ORDER BY, such a view is not editable (does not allow INSERT/UPDATE/DELETE). You can try to for example change artist 'AC/DC' to 'Le Matos'. In this step, we will attempt to make this view editable.
The most important and at the same time the most difficult part of trigger creation for editable view is to find the right and appropriate "translation" of the DML operation over the view into the tables that use it. In the context of the current task it is question how the change of name of artist 'AC/DC' to name 'Le Matos' will be transfered to tables Artist and Track which are used in this view.
Task 1.2
Write a trigger that will allow UPDATE on allArtists view.
Comment
The trigger will be INSTEAD OF, replacing the update over the view with some action over the tables from which the view is based on.
Comment
Changing the name of the artist will be interpreted as change in both tables of Artist and Track. We will change all occurences of composer in Track table and name of composer in Composer table (e.g. if 'AC/CD' is artist and also composer, update on allArtists view should change name in table Artist and all occurences of composer in table Track).
Task 1.3
Write a new trigger, which will allow INSERT on allArtist view (or edit previous one, so it can do both operations).
Comment
Inserting of a new name will be interpreted as inserting of new artist. Therefore if someone will try to insert into allArtists view some record it will be inserted in Artist table. Do not forget to insert also artistid incremented by one from the current maximum of artistid (e.g. if actual max artistid is 275 you will set new artistid to 276).
Comment
If you are modifiyng trigger from previous task, you can see this link in order to know how to use conditional predicates for INSERTING, DELETING, UPDATING to distinguish whether the trigger is currently running for INSERT, UPDATE, or DELETE.
Task 1.4
Check the correct implementation of both triggers by adding a new record (INSERT) and updating an existing (UPDATE).
Step 2
In this step, we will alter the implementation of previous triggers in order to maintain the consistency of allArtist view. If a new artist name entry is inserted and such artist already exist, records from allArtist view does not change as UNION will remove all duplicates. However, a new record will still be created in the artist table.
Task 2.1
Modify the trigger allowing INSERT over allArtist view, so that it is executed only if in allArtist view does not exist artist with name the user is trying to insert.
Comment
E.g. in the database and therefore also in the view there is artist with the name 'AC/DC'. If so in such a state somebody tries to insert a new entry with the name 'AC/DC' into allAritst view, throw your own custom exception and don't execute INSERT (exception must be declared first - its name can be for example 'prohibitedOperation').
Comment
Perhaps the easiest way to find out whether or not there is an artist with that name is to declare a numberic variable and retrieve the number of artists in the allArtist view, whose name is the same as the name of the artist just inserted. If the number is bigger than 0, it means that such artist is already there and we need to throw the exception.
The same problem as with INSERT can happen even with UPDATE - if you try to change the name of ane oartist to the name which already exists. Allowing such a change would appear to be a deletion of the artist from the database as duplicate name would be discarded in view as duplicate and it would show only once in results, which is not what we want.
Task 2.2
Edit the trigger for UPDATE over allArtist view so that if the new artist name is already occupied, nothing happens.
Comment
You can use the same logic again for this adjustment as in the previous task. First count how many times the name is already used, and edit the name only if that name is not used yet.
Again, test the proper implementation with appropriate INSERT and UPDATE statements. Although allowing DELETE statement would not be much more demanding, I will leave it as an additional task. Now let's go to next step, where we will create interesting trigger, which will take care of business logic of our e-shop domain.
Step 3
In this step your task will be more challenging. Let's say when we create a new customer, we want him to have good customer support and therefore we need to assign him and supporting employee. The company's internal policy is such that every "Sales Support Agent" employee should have same number of customers or at least same number - distribution of customers between employees should be fair.
Task 3.1
Write a trigger that sets 'supportRepId' for when a new customer id added to the database. And supporting employee should be selected one of Sales Support Agent which has least customers. If everybody has same number of customers, employee will be selected randomly.
Comment
The trigger itself is not very difficult, but it is necessary to write the correct select, which will return id of employee which fulfills our conditions (has the lowest number of supported customers and is working as 'Sales Support Agent'). Start by trying to write a select, which will return such employee id.
Thanks to this trigger, it is impossible for a new customer to be without proper customer support.
Additional Tasks
Task A.1
Chinook
Write a trigger to allow deletion of artist from allArtists view.
Comment
We will interpret the deletion as follows: If there is a composer with a given name in the track table, the given composer in the Track table will be set to NULL. If there is artist in table Artist with that name, its record will be deleted. Bewar: to table Artist can reference one or more albums from Album table, so first you must delete all albums that refer to that artist. You do not have to cascade delete all tracks from album, when you are deleting album, only set albumId to NULL in track table for each track from deleted album.