I'm trying to update 2 fields in multiple tables in my database using a trigger after inserting a new row

user_10859477 0
Viewed 67

i just created the trigger

here is the code:

CREATE OR REPLACE TRIGGER tr_update_solde_nbreserv
AFTER INSERT ON reservation
FOR EACH ROW
BEGIN
    UPDATE CLIENTS
    SET CLIENTS.NB_RESERV = NB_RESERV+1 , CLIENTS.SOLDE = SOLDE+1
    FROM dbo.clients as c
    INNER JOIN dbo.reservation as r
    ON c.numc = r.numc
    WHERE r.numr = :new.numr;
          
    BEGIN
        SELECT fillHist_station FROM DUAL;
    END;
    
end tr_update_solde_nbreserv;

i'm getting these 2 errors:

Error(1,9): PL/SQL: SQL Statement ignored

Error(3,9): PL/SQL: ORA-00933: SQL command not properly ended

how can i fix them

plsql oracle
2 Answers

There are several issues with your trigger code.

1) The purpose of this part of the code is unclear : 

BEGIN
    SELECT fillHist_station FROM DUAL;
END;

fillHist_station is not declared, hence this code would raise an invalid identifier error.

2) The syntax of the UPDATE query is invalid. Also, your intend is to select from the table that the trigger fired on, which is not allowed in Oracle. From looking at your code, it looks like you don't actually need to query RESERVATION to achieve your goal. As a row was just inserted in RESERVATION, you already know the corresponding client number.

3) You miss a / at the end

Here is an updated version of your code, that you can play around with in this db fiddle (you did not provide the structure of your tables so I just created the coulmns that are referenced in the query) :

CREATE OR REPLACE TRIGGER tr_update_solde_nbreserv
AFTER INSERT ON RESERVATION
FOR EACH ROW
BEGIN 
    UPDATE CLIENTS
    SET NB_RESERV = NB_RESERV+1 , SOLDE = SOLDE+1
    WHERE numc = :new.numc; 
END tr_update_solde_nbreserv;
/
user_106767160 
update CLIENTS
    SET CLIENTS.NB_RESERV = NB_RESERV+1 , CLIENTS.SOLDE = SOLDE+1
    FROM dbo.clients as c
    inner join dbo.reservation as r
        on c.numc = r.numc
        where r.numr = :new.numr;

update with inner join is not supported in Oracle database in this way. furthermore dbo.clients and dbo.reservations look like Sql Server tables rather then oracle.

I believe that you are looking for something like this, but I am not sure about the relations. You may need to fix the query.

UPDATE 
(
   SELECT clients.nb_reserv, r.nb_reserv as r_nb_reserv, clients.solde, r.solde as r_solde 
   FROM clients
   inner join reservation as r
     on c.numc = r.numc
   where r.numr = :new.numr
) t
SET CLIENTS.NB_RESERV = r_nb_reserv + 1, clients.solde = r_solde+1;

Simpler approach

UPDATE clients SET NB_RESERV = (SELECT nb_reserv +1
                                  FROM reservations 
                                  WHERE c.numc = r.numc and r.numr = :new.numr),
    SOLDE = (SELECT SOLDE +1
                                  FROM reservations 
                                  WHERE c.numc = r.numc and r.numr = :new.numr)
WHERE  EXISTS (SELECT 1 FROM reservations 
     WHERE c.numc = r.numc and r.numr = :new.numr);
user_11189780
Related