SQL ORACLE error in trigger

Question

I'm trying to create a trigger and I get the following error:

Error(24,5): PLS-00103: Found the symbol "BEGIN" when it was expected one of the following: * & - + / at loop mod remainder rem and or || multiset. I'm quite a newbie, thanks in advance!

CREATE OR replace TRIGGER ins_livro
      instead OF INSERT ON viewLivros
      FOR EACH ROW
    DECLARE 
    cnt NUMBER := 10;
    biggestID Number;
    BEGIN 
        Select max(exemplar_id) into biggestID from exemplar;
        INSERT INTO livro (
                      id_livro, 
                      nome_livro,
                      id_editora,
                      ano,
                      Preco_Aluguer,
                      Preco_Compra,
                      Preco_Multa
                    ) 
        VALUES      (:new.id_livro, 
                     :new.nome_livro, 
                     :new.id_editora, 
                     :new.ano, 
                     :new.Preco_Aluguer, 
                     :new.Preco_Compra, 
                     :new.Preco_Multa 
                    ); 
        WHILE cnt > 0
        BEGIN
          SET biggestID = biggestID + 1
          INSERT INTO exemplar (
                      id_exemplar,
                      id_livro
                      )
          VALUES      (
                      :new.biggestID,
                      :new.id_livro
                      );
          SET cnt = cnt - 1
        END;
    END;

Mureinik · Accepted Answer · 2015-05-20 13:38:12Z

2

You are missing the loop and end loop clauses:

WHILE cnt > 0
LOOP
BEGIN
      SET biggestID = biggestID + 1
      INSERT INTO exemplar (
                  id_exemplar,
                  id_livro
                  )
      VALUES      (
                  :new.biggestID,
                  :new.id_livro
                  );
      SET cnt = cnt - 1
    END;
END LOOP;

answered May 20, 2015 at 13:38

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

The begin/end aren't really adding anything here anyway, of course. Without an exception clause an inner block seems a bit pointless.

Mark Leiber · Accepted Answer · 2015-05-20 16:39:50Z

You have a few mistakes in your syntax. Here it is corrected:

CREATE OR REPLACE TRIGGER ins_livro INSTEAD OF
  INSERT ON viewLivros FOR EACH ROW DECLARE cnt NUMBER := 10;
  biggestID NUMBER;
  BEGIN
    SELECT MAX(exemplar_id) INTO biggestID FROM exemplar;
    INSERT
    INTO livro
      (
        id_livro,
        nome_livro,
        id_editora,
        ano,
        Preco_Aluguer,
        Preco_Compra,
        Preco_Multa
      )
      VALUES
      (
        :new.id_livro,
        :new.nome_livro,
        :new.id_editora,
        :new.ano,
        :new.Preco_Aluguer,
        :new.Preco_Compra,
        :new.Preco_Multa
      );
    WHILE cnt > 0
    LOOP
      BEGIN
        biggestID := biggestID + 1;
        INSERT
        INTO exemplar
          (
            id_exemplar,
            id_livro
          )
          VALUES
          (
            biggestID,
            :new.id_livro
          );
        cnt := cnt - 1;
      END;
    END LOOP;
  END;

Issues:

You can't use this syntax: SET biggestID = biggestID + 1

You need to use: biggestID := biggestID + 1;

Notice the SET keyword has been removed, = has been changed to := and the line has been terminated with a semicolon.

Also, there's no such thing as :new.biggestID. That is a variable that you've defined in the trigger. It needed to be replaced by just biggestID.

Finally, the BEGIN and END around this block were replaced with LOOP and END LOOP:

 SET biggestID = biggestID + 1
      INSERT INTO exemplar (
                  id_exemplar,
                  id_livro
                  )
      VALUES      (
                  :new.biggestID,
                  :new.id_livro
                  );
      SET cnt = cnt - 1

Collectives™ on Stack Overflow

SQL ORACLE error in trigger

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related