Create and populate a column called UCID_Total_G_Played (where UCID needs to be your (UCID) and a column called UCID_Career_Range_Factor in the PEOPLE table. Next populate both columns with the appropriate aggregate functions for each player. Total _G_Played is simply the sum of all the G columns for a player in the FIELDING table. Career_Range_Factoris calculated using the following formula: Career_Range_Factor (RF) = 9*sum(P+A)/(sum(InnOuts)/3). Your SQL will need to adjust the columns and results for any difficulties caused by the column data ypes. The performance factor indicates if a player helps others on his team play better (RF > 1) or takes away from their performance (RF < 1). The 3rd step is to write a trigger that updates the both of the columns you created in the PEOPLE table whenever there is a row inserted, updated or deleted from the FIELDING table. The trigger name must start with your UCID and the DDL that creates the trigger must also check to see if the trigger exists before creating it. The trigger must use basic math functions (+, -) to adjust UCID_Total_G_Played. You'll need to use the INSERTED and DELETED tables to get the values to add or subtract. You can use the
appropriate aggregate functions and the FIELDING table to adjust/recalculate the UCID_Career_Range_Factor column correctly. Your answer must also include the queries necessary to verify your trigger works correctly. This would typically include 3 sets of queries. One each for Insert, Delete and Update commands. Each set would have the following pattern. The firsts query would select the columns from the PEOPLE and FIELDING tables. The 2nd query would perform the insert, update or delete function on the FIELDING table. The 3rd query would select the columns from the PEOPLE and FIELDING tables to show that your trigger correctly updated the values changed in the 2nd query. The 3 sets needed would be separate queries for insert, update and delete . The last part of your submission needs to be the DDL to disable the trigger. The trigger will be 80% of the grade and the queries to test your trigger will be 20% of the grade.

Answer rating: 100% (QA)

Solution CREATE TRIGGER ucidtrigger AFTER INSERT UPDATE DELETE ON fielding FOR EACH ROW ... View the full answer