When I insert into a table, how do I create a trigger such that the inserted numeric value is summed-CodePudding

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?

CodePudding user response：

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','[email protected]','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	[email protected]	job1	1

fiddle

CodePudding user response：

The CREATE TRIGGER documentation gives you details of the syntax of a CREATE TRIGGER statement.

To solve your problem, you need:

A trigger on the CabinCrew table.
That triggers on UPDATE and INSERT (and, maybe DELETE) and FOR EACH ROW modified.
Then in the body of the trigger you need to UPDATE the Employee table for the modified EmployeeId and add the new Hours (which will be NULL for a delete statement) and subtract the old Hours (which will be NULL for an insert statement) from the existing flight hours.