1. Write a stored procedure called ChangeAuthor. It should take the following parameters:

@title_id (title for which you are going to change the author and royalty values)

@au_id (new author for the title)

@advance (new value for the advance column in the royalties table)

@royalty_rate (new value for the royalty_rate column in the royalties table)

The stored procedure should update the title_author and royalties tables for a particular title. Specifically, the title_author table should be updated with a new au_id and the royalties table should be updated with new advance and royalty_rate values.

The motivation is that after a publisher already negotiated with a particular author to write a specific book (e.g. "Into to Visual Basic") entries might have been made already for the book in the title_authors, titles and royalties tables. If the relationship with the original author sours, the publisher might negotiate a new contract with a different author to write the same book. The publisher will then have to modify the terms of the contract for the 2nd author. The point is that you don't want to allow the author for a book to change without simultaneously specifying the new advance and royalty_rate. This stored procedure will do all that is one shot. (see part 2 for an important addition to this scheme).

The procedure should return 0 on success and 1 if anything went wrong.The entire procedure should be done inside a transaction and the transaction should be rolled back if any sql statement fails.

2. Write a trigger that will not allow any changes to the au_id column of a record in the title_authors table unless it is being changed by the stored procedure from part (1).

You can do this by insisting that you can't modify the title_authors record if the royalties record exists. Then make sure that the stored procedure is coded so that the royalties record is deleted before the title_authors record is updated.