Home / Questions / 256437
Accepted
D. Albarracín
Asked: 2020-04-23 10:15:00 +0800 CST 2020-04-23 10:15:00 +0800 CST

Error with NEW and OLD in PL-SQL with triggers (Oracle)

  • 772

!Good!

I am doing this exercise:

When inserting or modifying a vendor, make sure that the id does not match any customer id. If it matches, it will be inserted, but it will be recorded in a new table as a log.

Where the relational model looks like this:

Clientess (id, nombre)
Vendedores (id, nombre)
Ventas (id_clienteFK, id_vendedorFK, importe, fecha)
tabla_log(id,tipo,descripción)

And trying to perform the exercise, I'm trying with this solution:

CREATE OR REPLACE TRIGGER insertar_vendedor BEFORE INSERT OR UPDATE
ON vendedores
DECLARE 
idCliente number;
BEGIN
select id into idCliente from clientess where clientess.id = :new.id;
if (idCliente%FOUND) then
INSERT INTO tabla_log values (1,'insertar','Se ha insertado un vendedor');
ELSIF (idCliente%NOTFOUND) THEN
DBMS_OUTPUT.PUT_LINE('No hay conincidencia');
END if;
END;

But this error has popped up, and the truth is that I'm starting with Triggers and I'm pretty lost... Let's see if someone knows how to structure this exercise.

Mistake:

enter image description here

oracle

1 Answers

  1. Best Answer

    I think you should use FOR EACH ROW in your trigger so that it's done at the row level, i.e. so that it fires every time you try to insert or update.

    CREATE OR REPLACE TRIGGER insertar_vendedor BEFORE INSERT OR UPDATE
ON vendedores
FOR EACH ROW
DECLARE 
    idCliente number;
BEGIN
    select id into idCliente from clientess where clientess.id = :new.id;
    INSERT INTO tabla_log values (1,'insertar','Se ha insertado un vendedor');

    exception
      when no_data_found 
      then DBMS_OUTPUT.PUT_LINE('No hay conincidencia para id: ' || :new.id);

END;

    I edit because asking for FOUND or NOTFOUND in a SELECT INTOsimply doesn't do anything. If there are no matches, it will throw an exception, which is what would be handled in the exception block. [answer on SO in English]

    • 2