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

Your trigger was not properly compiled at first, hence it fails to execute. Check for compilation erros : select * from user_errors where type = 'TRIGGER' and name = 'TUPDATE_HIST_STATION'; — GMB
What dd the show errors say? If it ran, that is - do you have a slash (/) after the end;? The block structure looks off though, you might want to review that. You may just want another begin before the first one you have now, but it would still look a bit odd. — Alex Poole
"s.NUM_STATION =: NEW.NUM_STATION" -- that colon seems to be stray and wants to go back to NEW. It also seems like you miss one BEGIN, the very first one as the others seem to belong to the sub blocks. It would be easier to spot if you used indentation for the sub blocks. — sticky bit
If the tool you are using for development does not clearly highlight compilation errors then you should consider switching to a different tool. — William Robertson

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2019-01-12T03:26:25

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.

I just created this trigger in plsql

1 Answers