The following schema is given (primary keys are bold): ARTWORK (ArtworkCode. Title, Author, BasicAuction Price, FinalSalePrice". NumParticipantingBuyers", FinalBuyerID") OFFER (ArtworkCode, BuyerID, Date, Amount) REQUEST AUCTION ENDING (ArtworkCode. Auction EndingDate, Description) Write the trigger to manage the following activities related to an online auction of artworks. The ARTWORK table contains the list of artworks sold at the auction. The OFFER table contains, for each artwork, a list of the amounts that have been offered by buyers interested in purchasing the artwork. The same buyer can make multiple offers for the same artwork on different dates. A request to end the auction for an artwork is generated (inserting a record into the table REQUEST AUCTION ENDING), If there was no offer for the artwork, the trigger ends with an error. Otherwise, you have to calculate the following information. (1) The final sale price of the artwork, corresponding to the highest amount offered for the artwork, 2) the buyer who offered this amount. Consider that there is only one maximum offer amount for each artwork. 3) The number of different buyers who participated in the auction. You must then update the value of the FinalSalePrice, NumParticipantingBuyers, FinalBuyerID attributes in the ARTWORK table with the calculated Information. INstruction for carrying out the question: Given the following incomplete solution of the trigger, you are asked to complete Part A in bold by specifying the trigger execution mode (before or after) and Part B in bold by specifying the body of the trigger. Use the text box below to provide your solution. Use the raise application_error() function to raise an error. It is not required to specify the parameters passed to the function.
The following schema is given (primary keys are bold):
ARTWORK (ArtworkCode. Title, Author, BasicAuction Price, FinalSalePrice". NumParticipantingBuyers", FinalBuyerID")
OFFER (ArtworkCode, BuyerID, Date, Amount)
REQUEST AUCTION ENDING (ArtworkCode. Auction EndingDate, Description)
Write the trigger to manage the following activities related to an online auction of artworks. The ARTWORK table contains the list of artworks sold at the auction.
The OFFER table contains, for each artwork, a list of the amounts that have been offered by buyers interested in purchasing the artwork. The same buyer can make multiple offers for the same artwork on different dates.
A request to end the auction for an artwork is generated (inserting a record into the table REQUEST AUCTION ENDING), If there was no offer for the artwork, the trigger ends with an error. Otherwise, you have to calculate the following information.
(1) The final sale price of the artwork, corresponding to the highest amount offered for the artwork,
2) the buyer who offered this amount. Consider that there is only one maximum offer amount for each artwork.
3) The number of different buyers who participated in the auction.
You must then update the value of the FinalSalePrice, NumParticipantingBuyers, FinalBuyerID attributes in the ARTWORK table with the calculated Information.
INstruction for carrying out the question:
Given the following incomplete solution of the trigger, you are asked to complete Part A in bold by specifying the trigger execution mode (before or after) and Part B in bold by specifying the body of the trigger. Use the text box below to provide your solution. Use the raise application_error() function to raise an error. It is not required to specify the parameters passed to the function.
create or replace trigger EndingAuction after insert on REQUEST AUCTION ENDING
Part A
Part B
Step by step
Solved in 2 steps
