Trigger is not called on update table in oracle

I’m trying to make a trigger that updates the ID_ABONAMENT from the CONTRACTE table, when the ABONATI table is updated.

The problem is that the trigger is not called when I update a row in the ABONATI table.

Below is the trigger I made. Thank you in advance.

 CREATE OR REPLACE TRIGGER UPDATE_CONTRACT_TRIGGER
AFTER UPDATE ON ABONATI
FOR EACH ROW
DECLARE
    id_abonat number;
    cod_abonament number;
BEGIN
    id_abonat := :new.ID_ABONAT;
    cod_abonament := :new.COD_ABONAMENT;
    UPDATE CONTRACTE SET COD_ABONAMENT = cod_abonament WHERE ID_ABONAT = id_abonat;
END;

Thank you in advance.

Answer

You actually have a completely different problem – the trigger is being called but it’s updating every row, each time it’s updating a column to itself.

set COD_ABONAMENT = cod_abonament

WHERE ID_ABONAT = id_abonat;

These are going to be evaluated as referring to the columns in your table rather than the variables. So this is going to evaluate to true for every row which has an id_abonat value that isn’t null and set the column to itself. You need to either use a different variable name or fully specify it in your update statement.

I would just forget about using a user defined variable here and just use :new.id_abonat in your update statement as is:

CREATE OR REPLACE TRIGGER UPDATE_CONTRACT_TRIGGER
AFTER UPDATE ON ABONATI
FOR EACH ROW
BEGIN
    UPDATE CONTRACTE SET COD_ABONAMENT = :new.COD_ABONAMENT WHERE ID_ABONAT = :new.ID_ABONAT;
END;
/