When I insert into a table, how do I create a trigger such that the inserted numeric value is summed up in another table's column?

Question

I have a table CabinCrew and another table Employee. The CabinCrew table, among other things, records the FlightHours of the crew for a particular flight.

CabinCrew(EmployeeID, FlightID, Hours)

Employee(EmployeeID, FirstName, LastName, Email, JobTitle, FlightHours)

EmployeeID is the primary key of the Employee table and both the primary and foreign key of the CabinCrew table.

The Employee table also has an attribute FlightHours, which is the total flying hours for the crew. How do I create a trigger such that an entry in the CabinCrew table, triggers an update on the Employee table which adds the FlightHours from CabinCrew to the total flight hours in the Employee table?

Answer 1

you need an AFTERT INSERT trigger.

CREATE TABLe CabinCrew (EmployeeID int, FlightID int, Hours int)

CREATE TABLE Employee(EmployeeID int , FirstName varchar2(50)
  , LastName varchar2(50), Email varchar2(50), JobTitle varchar2(50), FlightHours int)

INSERT INTO Employee VALUES(1,'test1','test2','test@test.com','job1',0)

1 rows affected

CREATE OR REPLACE TRIGGER CabinCrew_after_insert
AFTER INSERT
   ON CabinCrew
   FOR EACH ROW
DECLARE
   V_EMPLOYEEID int;
BEGIN
   SELECT "EMPLOYEEID" INTO V_EMPLOYEEID FROM Employee WHERE "EMPLOYEEID" = :new.EMPLOYEEID;
IF V_EMPLOYEEID IS NULL THEN
   RAISE_APPLICATION_ERROR(-20001, 'Employee unknown');
END if;
  UPDATE Employee SET FLIGHTHOURS = FLIGHTHOURS + :new.HOURS
    WHERE "EMPLOYEEID" = :new.EMPLOYEEID;
END CabinCrew_after_insert;
/

SELECT * FROM USER_ERRORS;

INSERT INTO CabinCrew VALUEs(1,1,1)

1 rows affected

INSERT INTO CabinCrew VALUEs(2,1,1)

ORA-01403: no data found
ORA-06512: at "FIDDLE_HHVVQEUNGIIOLEAXDXQH.CABINCREW_AFTER_INSERT", line 4
ORA-04088: error during execution of trigger 'FIDDLE_HHVVQEUNGIIOLEAXDXQH.CABINCREW_AFTER_INSERT'

SELECT * FROM Employee

EMPLOYEEID	FIRSTNAME	LASTNAME	EMAIL	JOBTITLE	FLIGHTHOURS
1	test1	test2	test@test.com	job1	1

fiddle