CODEWITHSUNDEEP
sqloracle-database
Greiko
Greiko

Reputation: 17

sql pls-00103 code errors

CREATE OR REPLACE TRIGGER verifier_salle_pavillon
AFTER INSERT OR UPDATE ON Salle
FOR EACH ROW
BEGIN
IF (:NEW.id_salle = (select *
                    from Salle
                    where id_pavillon = :OLD.id_pavillon;)) 
THEN
DELETE FROM Salle
WHERE id_salle = :NEW.id_salle;
END IF;
END;
/

And I get this Error: 

4/42     PLS-00103: Encountered the symbol ";" when expecting one of the
 following:
 . ( ) * @ % & - + / at mod remainder rem with <exposant (**)>
 and or group having intersect minus start union where connect
 || indicator multiset
 The symbol ";" was ignored.

The error is at line 4? What's wrong with BEGIN?

What is wrong with that trigger? I just want to delete the row when there is already a row having the same id_pavillon and id_groupe.

Upvotes: 0

Views: 377

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The parsing problem is the semicolon in this part:

IF (:NEW.id_salle = (select *
                    from Salle
                    where id_pavillon = :OLD.id_pavillon;)) 
--------------------------------------------------------^

I should say "the first problem". The code = (select * is highly unorthodox and quite likely to produce errors.

You can write this without the if:

DELETE FROM Salle
WHERE id_salle = :NEW.id_salle AND
      EXISTS (SELECT 1 FROM Salle WHERE id_pavillon = :OLD.id_pavillon);

This might require another condition, depending on what you intend by the original if condition.

Upvotes: 1

Related Questions