Create a trigger named trg_char_hours that automatically updates 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. (Hint: use temp values, as the INSERT event does not accept the OLD keyword.)
Create a trigger named trg_char_hours that automatically updates 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. (Hint: use temp values, as the INSERT event does not accept the OLD keyword.)
Create a trigger named trg_char_hours that automatically updates 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. (Hint: use temp values, as the INSERT event does not accept the OLD keyword.)
I'm not sure if I'm running the correct Query for this problem.
The question is:
"Create a trigger named trg_char_hours that automatically updates 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.
(Hint: use temp values, as the INSERT event does not accept the OLD keyword.)"
I ran this query without any errors:
CREATE TRIGGER trg_char_hours
AFTER INSERT ON CHARTER
FOR EACH ROW
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;
But the answer is still incorrect because the last row doesn't have the expected values. I'm not sure why the test query would delete any row with a CHAR_TRIP ABOVE 10018. I attached a screen of the output I'm getting:
I saw in another answer this:
Expert Answer
Step 1
Actually, given information is
"Create a trigger named trg_char_hours that automatically updates 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.
Step 2
1. The Trigger is given below:- CREATE OR REPLACE TRIGGER trg_char_hours AFTER INSERT ON CHARTER FOR EACH ROW
AS
BEGIN
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.
DELIMITER </strong></p><p><strong>CREATEORREPLACEPROCEDUREUPDATEMODELCHARGE(MODELNOINMODEL.MODCODE%TYPE)</strong></p><p><strong>BEGIN</strong></p><p><strong>UPDATEMODELSETMODCHGMLE=1.20*MODCHGMLEWHEREMODEL.MODCODE=MODELNO;</strong></p><p><strong>COMMIT;</strong></p><p><strong>END</strong></p> <p><strong>CREATE OR REPLACE PROCEDURE UPDATE_MODEL_CHARGE (MODEL_NO IN MODEL.MOD_CODE%TYPE)</strong></p> <p><strong>BEGIN</strong></p> <p><strong>UPDATE MODEL SET MOD_CHG_MLE = 1.20 * MOD_CHG_MLE WHERE MODEL.MOD_CODE = MODEL_NO;</strong></p> <p><strong>COMMIT;</strong></p> <p><strong>END
DELIMITER ;
But this answer is also incorrect. I attached a second screcond of Expert solution.
Transcribed Image Text:CENGAGE MINDTAP
Q Search this course
Problems 8.35 - 8.46 (MYSQL)
Problems 8.44-8.46
query.sql
+
Compiete Propiem 44
68
Feedback ®
69 CREATE OR REPLACE TRIGGER trg char hours
A-2
Lalal
70 AFTER INSERT
Some expected rows were missing (shown in red below).
71 ON CHARTER
72 FOR EACH ROW
</>
Some unexpected rows were returned.
73 AS
Test Query ®
74 BEGIN
75 UPDATE AIRCRAFT
76 SET
UPDATE AIRCRAFT SET AC_TTAF = 1833.1, AC_TTEL = 1833.1, AC_TTER = 101.8 WHERE AC_NUM
77 AC_TTAF = AC_TTAF + :NEW.CHAR_HOURS_FLOWN,
INSERT INTO CHARTER VALUES('10019','2018-02-10','1484P','TYS','644',' 10','4.5','167.
78 AC_TTEL = AC_TTEL + :NEW.CHAR_HOURS_FLOWN,
SELECT * FROM AIRCRAFT
79 AC_TTER = AC_TTER + :NEW.CHAR_HOURS_FLOWN
DELETE FROM CHARTER WHERE CHAR_TRIP > 10018
80 WHERE AIRCRAFT.AC_NUMBER = :NEW.AC_NUMBER;
UPDATE AIRCRAFT SET AC_TTAF = 1833.1, AC_TTEL = 1833.1, AC_TTER = 101.8 WHERE AC_NUM
81
bong
82 COMMIT;
83 END;
Expected Results e
84
AC_NUMBER
MOD CODE
AC_TTAF
AC TTEL
AC TTER
85
86 DELIMITER </strong></p><p><strong>CREATEORREPLACEPROCEDUREUPDATEMODELCHARGE (MODELNOINMODEL.MODCODE%TYPE)</strong></
p><p><strong>BEGIN</strong></p><p><strong>UPDATEMODELSETMODCHGMLE=1.20*MODCHGMLEWHEREMODEL.MODCODE=MODELNO; </strong></
1484P
PA23-250
1843.1
1843.1
111.8
2289L
C-90A
4243.8
768.9
1123.4
p><p><strong>COMMIT;</strong></p><p><strong>END
87 DELIMITER ;
2778V
РАЗ1-350
7992.9
1513.1
789.5
4278Y
PA31-350
2147.3
622.1
243.2
9 SQL Viewer
Actual Results e
AC NUMBER
MOD CODE
AC_TTAF
AC_TTEL
AC_TTER
1484P
PA23-250
1833.1
1833.1
101.8
ERROR 1064 (42000) at line 69: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'TRIGGER trg_char_hours AFTER INSERT ON CHARTER FOR EACH ROW AS BEGIN UPDATE
2289L
C-90A
4243.8
768.9
1123.4
AIR' at line 1
2778V
PA31-350
7992.9
1513.1
789.5
127RV
DA31 350
2151 1
625 9
247
Transcribed Image Text:Problems 8.44-8.46
query.sql
+
Some expected rows were missing (shown in red below).
55
56
Some unexpected rows were returned.
57 CREATE TRIGGER trg_char_hours
58 AFTER INSERT ON CHARTER
Test Query
O
59 FOR EACH ROW
60 UPDATE AIRCRAFT
UPDATE AIRCRAFT SET AC_TTAF = 1833.1, AC_TTEL = 1833.1, AC_TTER = 101.8 WHERE AC_NUMBER =
61 SET AC_TTAF - AC_TTAF + NEW.CHAR_HOURS_FLOWN,
INSERT INTO CHARTER VALUES('10019','2018-02-10','1484P',"TYS','644','10','4.5','167.4','e"
62 AC_TTEL - AC_TTEL + NEW.CHAR_HOURS_FLOWN,
SELECT * FROM AIRCRAFT
63 AC_TTER - AC_TTER + NEW.CHAR_HOURS_FLOWN
DELETE FROM CHARTER WHERE CHAR_TRIP > 10018
UPDATE AIRCRAFT SET AC_TTAF = 1833.1, AC_TTEL = 1833.1, AC_TTER = 101.8 WHERE AC_NUMBER =
64 WHERE AIRCRAFT.AC_NUMBER - NEW.AC_NUMBER;
65 */
66
67 select * from CHARTER WHERE AC_NUMBER - '4278Y order by CHAR_DATE;
Expected Results O
AC_NUMBER
MOD CODE
AC_TTAF
AC TTEL
AC_TTER
e SQL Viewer
1484P
PA23-250
1843.1
1843.1
111.8
CHAR_TRIP CHAR_DATE AC_NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOURS FLOWN CHAR_HOURS_WAIT CHAR_FUEL_
2289L
C-90A
4243.8
768.9
1123.4
2018-02-05
2778V
PA31-350
7992.9
1513.1
789.5
10003
4278Y
GNV
1574
7.8
339.8
00:00:00
4278Y
PA31-350
2147.3
622.1
243.2
2018-02-06
10006
4278Y
STL
472
2.6
5.2
117.1
00:00:00
Actual Results ®
2018-02-07
10010
4278Y
ATL
998
6.2
3.2
279.7
AC_NUMBER
MOD CODE
AC_TTAF
AC TTEL
AC_TTER
00:00:00
1484P
PA23-250
1843.1
1843.1
111.8
2018-02-08
10013
4278Y
TYS
644
3.9
4.5
174.3
00:00:00
2289L
C-90A
4243.8
768.9
1123.4
2018-02-09
10014
4278Y
ATL
936
6.1
2.1
302.6
2778V
PA31-350
7992.9
1513.1
789.5
00:00:00
4278Y
PA31-350
2151.1
625.9
247
2018-02-10
10018
4278Y
TYS
644
3.8
4.5
167.4
00:00:00
2018-02-10
NULL
4278Y
TYS
644
3.8
4.5
167.4
00:00:00
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
