Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
I think my query needs temp variables instead of using NEW but I am not sure how.   Here is my query: delimiter // DROP TRIGGER trg_pic_hours; create trigger trg_pic_hours after insert on CREW for each row begin IF NEW.CREW_JOB = 'Pilot' THEN update PILOT set PIL_PIC_HRS = PIL_PIC_HRS + ( select CHAR_HOURS_FLOWN from CHARTER where CHAR_TRP = NEW.CHAR_TRP) where EMP_NUM = NEW.EMP_NUM; END IF;   END //   If I run the query with the NEW.CHAR_TRP entry errors out if I remove NEW from it it will run ok. I have attached the schema

I think my query needs temp variables instead of using NEW but I am not sure how.   Here is my query: delimiter // DROP TRIGGER trg_pic_hours; create trigger trg_pic_hours after insert on CREW for each row begin IF NEW.CREW_JOB = 'Pilot' THEN update PILOT set PIL_PIC_HRS = PIL_PIC_HRS + ( select CHAR_HOURS_FLOWN from CHARTER where CHAR_TRP = NEW.CHAR_TRP) where EMP_NUM = NEW.EMP_NUM; END IF;   END //   If I run the query with the NEW.CHAR_TRP entry errors out if I remove NEW from it it will run ok. I have attached the schema

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
icon
Concept explainers
Question

I think my query needs temp variables instead of using NEW but I am not sure how.

 

Here is my query:

delimiter //

DROP TRIGGER trg_pic_hours;

create trigger trg_pic_hours

after insert on CREW

for each row

begin

IF NEW.CREW_JOB = 'Pilot' THEN

update PILOT

set PIL_PIC_HRS = PIL_PIC_HRS +

( select CHAR_HOURS_FLOWN from CHARTER where CHAR_TRP = NEW.CHAR_TRP)

where EMP_NUM = NEW.EMP_NUM;

END IF;

 

END //

 

If I run the query with the NEW.CHAR_TRP entry errors out if I remove NEW from it it will run ok.

I have attached the schema

FIGURE P8.35 CH08_AVIACO DATABASE TABLES Table name: CHARTER Database name: Ch08_AviaCo CHAR_TRIP CHAR_DATE AC_NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOLRS_FLOVIN CHAR_HDURS_WAIT CHAR_FUEL_GALLONS CHAR_OIL_QTS CUS_CODE 10001 05-Feb-18 2289L Table name: EARNEDRATING ATL 936 5.1 2.2 354.1 10011 10002 05-Feb-18 2778V BNA 320 1.6 72.6 10016 EMP_NUM RTG_CODE EARNRTG DATE 10003 10004 05-Feb-18 4278Y GNV 1574 7.8 339.8 2 10014 06-Feb-18 1484P STL 472 2.9 4.9 97.2 1 10019 101 CFI 18-Feb-98 10005 06-Feb-18 2289L ATL 1023 5.7 3.5 397.7 2 10011 101 CFII 15-Dec-05 10006 06-Feb-18 4278Y 472 26 5.2 117.1 10017 10012 STL 101 NSTR 08-Nov-93 10007 348.4 23-Jun.94 06-Feb-18 2778V CNV 1574 1.9 2 101 MEL 10008 07-Feb-18 1484P TYS 644 10014 4.1 140.8 101 SEL 21-Apr.93 15 Jul 96 10017 10016 10012 10010 10009 07-Fob-10 2209L. ONV 1574 6.6 23.4 459.9 104 NSTR 10010 07-Feb-10 4270Y 6.2 AIL 930 3.2 279.7 104 MEL 29-Jan-97 12-Mar 95 18-Nov-97 10011 07-Feb-18 1484P BNA 352 1.9 5.3 66.4 1 104 SEL 10012 08-Feb-18 2776V MOB 84 4.8 4.2 215.1 105 CFI 10013 08-Feb-18 4278Y TYS 644 3.9 4.5 174.3 1 10011 105 NSTR 17-Apr-95 12-Aug-95 10014 09-Feb-18 4278Y ATL 936 6.1 2.1 302.6 10017 105 MEL 10015 09-Feb-18 2289L GNV 1645 67 459.5 2 10016 105 SEL 23-Sep-94 10011 10014 10016 09-Feb-18 2778V MQY 312 1.5 67.2 106 NSTR 20-Dec-95 10017 10-Feb-18 1484P STL 508 3.1 105.5 106 MEL 02-Apr-96 10018 10-Feb-18 4278Y TYS 644 3.8 4.5 167.4 10017 106 SEL 10-Mar-94 109 CFI 05-Nov-98 109 CFI 109 NSTR 21-Jun-03 Table name: CREW Table name: CUSTOMER 23-Jul-96 109 MEL 15-Mar-97 CUS CCDE CUS_LNAME CLS_FNAME CUS_INITIAL CUS AREACODE CUS PHDNE CUS BALANCE 000 000 896 54 CHAR TRP EMP_NUM CREW JOB 109 SEL 05-Feb-96 104 Filot 101 Filot 10001 10010 Ramas Alfred 615 844-2573 109 SES 12-May-96 10002 10011 Dunne Leona 713 894-1238 894-2285 894-2180 222-1672 442-3381 10003 105 Filot 10012 Snith Kathy 615 10003 109 Coplot 10013 Obwski Paul F 615 1285.19 Table name: RATING 673 21 1014 56 10004 108 Filot 10014 Orlando Myron 615 10005 RTG CODE 101 Filot 10015 OBrian Amy James George 713 RTG NAME 10006 297-1228 290-2556 382-7185 109 Filot 10016 Brown 615 000 CFI Certified Flight Instructor Certified Flight Instructor, Instrument 10017 illiams 10018 Farriss 10007 104 Filot 615 00 CFI INSTR MEL 10007 105 Coplot Anne 713 000 Instrument 10008 106 Filot 10019 Snith Dlette 615 297-3809 453 98 Multiengine Land Single Engine, Land 10009 105 Filot SEL 10010 100 Filot SES Single Engine, Sea 101 Filot 104 Coplot Table name: EMPLOYEE 10011 10011 EMP_NUM EMP_TITLE EMP_LNAME 101 Filot 105 Filot EMP DOB 15-Jun-1942 19-Mar-1965 EMP FNAME EMP_INTIAL EMP HIRE DATE Table name: MODEL 10012 10013 100 Mr. Kolmycz George 15-Mar-1987 Lewe Vandam Jones 10014 105 Filot 101 Ms. Rhonda G 25-Apr-1988 MOD_CODE MOD_MANUFACTURER MOD NAME MOD_SEATS MOD_CHG_MILE 10015 10015 10016 101 Coplot 102 Mr. Rhett 14-Nov-1958 20-Dec-1992 C-90A Beechcraft KingAir 267 104 Pilot 103 Ms. Anne 16-Oct-1974 28-Aug-2005 PA23-250 Piper Azter 193 105 Coplot Lange Wlliems 104 Mr John P 08-Nov-1971 20-Oct-1996 PA31-350 Piper Navajo Chieftain 10 235 10016 10017 08-Jan-2006 05-Jan-1991 109 Filot 101 Filot 104 Coplot 105 Filot 105 Mr Robert D 14-Mar-1975 Duzak Diante 106 Mrs. Jeanne K 12-Feb-1968 10018 10018 21-Aug-1974 14-Feb-1966 107 Mr 02-Jul-1996 Jorge Wiesenbach Paul 108 Mr 18-Nov-1994 109 Ms. 110 Mrs Travis Elzabeth K 18-Jun-1961 14-Apr-1991 Genkazi Leighla 19-May-1970 01-Dec-1992 Table name: AIRCRAFT Table name: PILOT |AC NUMBER MOD CODE | AC TTAF AC TTEL AC_TTER EMP_NUM PIL LICENSE| FIL RATINGS PL MED TYPE PIL MED DATE PIL PT135 DATE PA23-250 C-90A РАЗ1-350 1833.1 4243.8 1484P 1833.1 101.8 101 ATP ATP/SELMELAnstr/CFII 1 20-Jan-18 11-Jan-18 2239L 18-Dec-17 05-Jan-18 768.9 1123.4 104 ATP 105 COM ATP/SELMELAnstr 1 17-Jan-18 2778Y 7992.9 2147.3 1513.1 789.5 COMM/SELMELAnstr/CFI 02-Jan-18 4278Y PA31-350 622.1 243.2 106 COM COMMISELMELInstr 2 10-Dec-17 02-Feb-18 109 COM ATP/SELMEL/SESAnstr/CFI 1 22-Jan-18 15-Jan-18
Transcribed Image Text:FIGURE P8.35 CH08_AVIACO DATABASE TABLES Table name: CHARTER Database name: Ch08_AviaCo CHAR_TRIP CHAR_DATE AC_NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOLRS_FLOVIN CHAR_HDURS_WAIT CHAR_FUEL_GALLONS CHAR_OIL_QTS CUS_CODE 10001 05-Feb-18 2289L Table name: EARNEDRATING ATL 936 5.1 2.2 354.1 10011 10002 05-Feb-18 2778V BNA 320 1.6 72.6 10016 EMP_NUM RTG_CODE EARNRTG DATE 10003 10004 05-Feb-18 4278Y GNV 1574 7.8 339.8 2 10014 06-Feb-18 1484P STL 472 2.9 4.9 97.2 1 10019 101 CFI 18-Feb-98 10005 06-Feb-18 2289L ATL 1023 5.7 3.5 397.7 2 10011 101 CFII 15-Dec-05 10006 06-Feb-18 4278Y 472 26 5.2 117.1 10017 10012 STL 101 NSTR 08-Nov-93 10007 348.4 23-Jun.94 06-Feb-18 2778V CNV 1574 1.9 2 101 MEL 10008 07-Feb-18 1484P TYS 644 10014 4.1 140.8 101 SEL 21-Apr.93 15 Jul 96 10017 10016 10012 10010 10009 07-Fob-10 2209L. ONV 1574 6.6 23.4 459.9 104 NSTR 10010 07-Feb-10 4270Y 6.2 AIL 930 3.2 279.7 104 MEL 29-Jan-97 12-Mar 95 18-Nov-97 10011 07-Feb-18 1484P BNA 352 1.9 5.3 66.4 1 104 SEL 10012 08-Feb-18 2776V MOB 84 4.8 4.2 215.1 105 CFI 10013 08-Feb-18 4278Y TYS 644 3.9 4.5 174.3 1 10011 105 NSTR 17-Apr-95 12-Aug-95 10014 09-Feb-18 4278Y ATL 936 6.1 2.1 302.6 10017 105 MEL 10015 09-Feb-18 2289L GNV 1645 67 459.5 2 10016 105 SEL 23-Sep-94 10011 10014 10016 09-Feb-18 2778V MQY 312 1.5 67.2 106 NSTR 20-Dec-95 10017 10-Feb-18 1484P STL 508 3.1 105.5 106 MEL 02-Apr-96 10018 10-Feb-18 4278Y TYS 644 3.8 4.5 167.4 10017 106 SEL 10-Mar-94 109 CFI 05-Nov-98 109 CFI 109 NSTR 21-Jun-03 Table name: CREW Table name: CUSTOMER 23-Jul-96 109 MEL 15-Mar-97 CUS CCDE CUS_LNAME CLS_FNAME CUS_INITIAL CUS AREACODE CUS PHDNE CUS BALANCE 000 000 896 54 CHAR TRP EMP_NUM CREW JOB 109 SEL 05-Feb-96 104 Filot 101 Filot 10001 10010 Ramas Alfred 615 844-2573 109 SES 12-May-96 10002 10011 Dunne Leona 713 894-1238 894-2285 894-2180 222-1672 442-3381 10003 105 Filot 10012 Snith Kathy 615 10003 109 Coplot 10013 Obwski Paul F 615 1285.19 Table name: RATING 673 21 1014 56 10004 108 Filot 10014 Orlando Myron 615 10005 RTG CODE 101 Filot 10015 OBrian Amy James George 713 RTG NAME 10006 297-1228 290-2556 382-7185 109 Filot 10016 Brown 615 000 CFI Certified Flight Instructor Certified Flight Instructor, Instrument 10017 illiams 10018 Farriss 10007 104 Filot 615 00 CFI INSTR MEL 10007 105 Coplot Anne 713 000 Instrument 10008 106 Filot 10019 Snith Dlette 615 297-3809 453 98 Multiengine Land Single Engine, Land 10009 105 Filot SEL 10010 100 Filot SES Single Engine, Sea 101 Filot 104 Coplot Table name: EMPLOYEE 10011 10011 EMP_NUM EMP_TITLE EMP_LNAME 101 Filot 105 Filot EMP DOB 15-Jun-1942 19-Mar-1965 EMP FNAME EMP_INTIAL EMP HIRE DATE Table name: MODEL 10012 10013 100 Mr. Kolmycz George 15-Mar-1987 Lewe Vandam Jones 10014 105 Filot 101 Ms. Rhonda G 25-Apr-1988 MOD_CODE MOD_MANUFACTURER MOD NAME MOD_SEATS MOD_CHG_MILE 10015 10015 10016 101 Coplot 102 Mr. Rhett 14-Nov-1958 20-Dec-1992 C-90A Beechcraft KingAir 267 104 Pilot 103 Ms. Anne 16-Oct-1974 28-Aug-2005 PA23-250 Piper Azter 193 105 Coplot Lange Wlliems 104 Mr John P 08-Nov-1971 20-Oct-1996 PA31-350 Piper Navajo Chieftain 10 235 10016 10017 08-Jan-2006 05-Jan-1991 109 Filot 101 Filot 104 Coplot 105 Filot 105 Mr Robert D 14-Mar-1975 Duzak Diante 106 Mrs. Jeanne K 12-Feb-1968 10018 10018 21-Aug-1974 14-Feb-1966 107 Mr 02-Jul-1996 Jorge Wiesenbach Paul 108 Mr 18-Nov-1994 109 Ms. 110 Mrs Travis Elzabeth K 18-Jun-1961 14-Apr-1991 Genkazi Leighla 19-May-1970 01-Dec-1992 Table name: AIRCRAFT Table name: PILOT |AC NUMBER MOD CODE | AC TTAF AC TTEL AC_TTER EMP_NUM PIL LICENSE| FIL RATINGS PL MED TYPE PIL MED DATE PIL PT135 DATE PA23-250 C-90A РАЗ1-350 1833.1 4243.8 1484P 1833.1 101.8 101 ATP ATP/SELMELAnstr/CFII 1 20-Jan-18 11-Jan-18 2239L 18-Dec-17 05-Jan-18 768.9 1123.4 104 ATP 105 COM ATP/SELMELAnstr 1 17-Jan-18 2778Y 7992.9 2147.3 1513.1 789.5 COMM/SELMELAnstr/CFI 02-Jan-18 4278Y PA31-350 622.1 243.2 106 COM COMMISELMELInstr 2 10-Dec-17 02-Feb-18 109 COM ATP/SELMEL/SESAnstr/CFI 1 22-Jan-18 15-Jan-18
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Query Syntax
Learn more about
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.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS