1. Create a trigger named trg_char_hours that will automatically update the AIRC
ID: 3731112 • Letter: 1
Question
1. Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values.
The meaning for the AIRCRAFT table columns are as follows:
AC_TTAF: Total time on the air frame
AC_TTEL: Total time on the left engine (Also used to record single engine hours) AC_TTER: Total time on the right engine.
So in the trigger, you need to increase all of them with value of CHAR_HOURS_FLOWN
2. Create a stored procedure to update model charge per mile attribute. Procedure takes the model number as a parameter. The procedure increases the charge for this model by 20%.
Explanation / Answer
AS
UPDATE AIRCRAFT
SET
AC_TTAF = AC_TTAF + :NEW.CHAR_HOURS_FLOWN,
AC_TTEL = AC_TTEL + :NEW.CHAR_HOURS_FLOWN,
AC_TTER = AC_TTER + :NEW.CHAR_HOURS_FLOWN
WHERE AIRCRAFT.AC_NUMBER = :NEW.AC_NUMBER;
COMMIT;
END;
/
2. CREATE OR REPLACE PROCEDURE UPDATE_MODEL_CHARGE
AS
BEGIN
UPDATE MODEL
SET
MOD_CHG_MLE = 1.20 * MOD_CHG_MLE
WHERE MODEL.MOD_CODE = MODEL_NO;
COMMIT;
END;
/
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.