I just created this trigger in plsql

Question

here is the code:

create or replace TRIGGER tupdate_hist_station
AFTER INSERT ON STATION
FOR EACH ROW
DECLARE
    V_num_station STATION.NUM_STATION%type;
    V_nb_reserve CLIENTS.NB_RESERV%type;
    V_revenue number;

    BEGIN

    SELECT s.NUM_STATION INTO v_num_station
    FROM STATION s
    WHERE s.NUM_STATION =: NEW.NUM_STATION;

    EXCEPTION
    WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE("There is no new data added");
    END;

    BEGIN

    SELECT sum(c.NB_RESERV) INTO V_nb_reserve 
    FROM CLIENTS c, RESERVATION r, CHAMBRE ch, HOTEL h, STATION s
    WHERE c.NUMC = r.NUMC AND r.IDCHAMBRE = ch.IDCHAMBRE AND ch.NUM_HOTEL = h.NUM_HOTEL
    AND h.NUM_STATION = s.NUM_STATION AND s.NUM_STATION = v_num_station;

    EXCEPTION
    WHEN no_data_found THEN V_nb_reserve:=0;    
    DBMS_OUTPUT.put_line("There is no reservation in a hotel in this station yet");
    END;

    BEGIN

    SELECT SUM(p.MONTANT) INTO V_revenue 
    FROM PAYEMENT p, CLIENTS c, RESERVATION r, CHAMBRE ch, HOTEL h, STATION s
    where p.NUMC = c.NUMC 
    AND c.NUMC = r.NUMC
    AND r.IDCHAMBRE = ch.IDCHAMBRE
    AND ch.NUM_HOTEL = h.NUM_HOTEL
    and h.NUM_STATION = s.NUM_STATION AND s.NUM_STATION = v_num_station;    

    EXCEPTION
    WHEN no_data_found THEN
    V_revenue := 0;
    DBMS_OUTPUT.put_line("There is no reservation in this station yet");    
    END;

    INSERT INTO HIST_STATION("NUM_STATION","ANNEE","NB_RESERV","REVENU") VALUES (V_num_station,(SELECT to_char(SYSDATE,'YYYY')FROM DUAL),V_nb_reserve,V_revenue);

END;

SHOW ERRORS;

when i insert values in station table i get these error:

INSERT INTO "DANIEL"."STATION" (NUM_STATION, NOM_STATION, ALTITUDE, REGION) VALUES ('6', 'Baalbak', '1250', 'Baalbak')

ORA-04098: trigger 'DANIEL.TUPDATE_HIST_STATION' is invalid and failed re-validation

ORA-06512: at line 1

One error saving changes to table "DANIEL"."STATION":

Row 6: ORA-04098: trigger 'DANIEL.TUPDATE_HIST_STATION' is invalid and failed re-validation

ORA-06512: at line 1

instead of inserting a new row in Hist_station

Answer 1

As others have noted, there are various errors. The biggest problem, though, is that the code that inserts data into HIST_STATION is not part of the trigger - it's just wandering around out in space somewhere.

When I started adjusting the indentation of your code to figure out what was going on, here's what I got:

create or replace TRIGGER tupdate_hist_station
  AFTER INSERT ON STATION
  FOR EACH ROW
DECLARE
  V_num_station STATION.NUM_STATION%type;
  V_nb_reserve  CLIENTS.NB_RESERV%type;
  V_revenue     number;
BEGIN
    SELECT s.NUM_STATION
      INTO v_num_station
      FROM STATION s
      WHERE s.NUM_STATION =: NEW.NUM_STATION;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE("There is no new data added");
END;

Your trigger ends, right there, after the first END. All the other code is just...gone.

I think that what you wanted was:

create or replace TRIGGER tupdate_hist_station
  AFTER INSERT ON STATION
  FOR EACH ROW
DECLARE
  V_num_station STATION.NUM_STATION%type;
  V_nb_reserve  CLIENTS.NB_RESERV%type;
  V_revenue     number;
BEGIN
  BEGIN
    SELECT s.NUM_STATION
      INTO v_num_station
      FROM STATION s
      WHERE s.NUM_STATION = :NEW.NUM_STATION;
  EXCEPTION
    WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE("There is no new data added");
  END;

  BEGIN
    SELECT sum(c.NB_RESERV)
      INTO V_nb_reserve 
      FROM CLIENTS c,
           RESERVATION r,
           CHAMBRE ch,
           HOTEL h,
           STATION s
      WHERE c.NUMC = r.NUMC AND
            r.IDCHAMBRE = ch.IDCHAMBRE AND
            ch.NUM_HOTEL = h.NUM_HOTEL AND
            h.NUM_STATION = s.NUM_STATION AND
            s.NUM_STATION = v_num_station;
  EXCEPTION
    WHEN no_data_found THEN
      V_nb_reserve:=0;    
      DBMS_OUTPUT.put_line("There is no reservation in a hotel in this station yet");
  END;

  BEGIN
    SELECT SUM(p.MONTANT) INTO V_revenue 
      FROM PAYEMENT p,
           CLIENTS c,
           RESERVATION r,
           CHAMBRE ch,
           HOTEL h,
           STATION s
      where p.NUMC = c.NUMC AND
            c.NUMC = r.NUMC AND
            r.IDCHAMBRE = ch.IDCHAMBRE AND
            ch.NUM_HOTEL = h.NUM_HOTEL and
            h.NUM_STATION = s.NUM_STATION AND
            s.NUM_STATION = v_num_station;    
  EXCEPTION
    WHEN no_data_found THEN
      V_revenue := 0;
      DBMS_OUTPUT.put_line("There is no reservation in this station yet");    
  END;

  INSERT INTO HIST_STATION
    ("NUM_STATION","ANNEE","NB_RESERV","REVENU")
  VALUES
    (V_num_station,(SELECT to_char(SYSDATE,'YYYY')FROM DUAL),V_nb_reserve,V_revenue);
END tupdate_hist_station;
/
SHOW ERRORS;
/

Best of luck.