CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Andrei Jitaru
Andrei Jitaru

Reputation: 43

sql error trigger instead of

I am trying to make an instead of trigger but I keep getting this error: Error at line 11: PL/SQL: SQL Statement ignored

Here is my code:

CREATE VIEW BiletNouVIP AS
SELECT nume, nrbilet, sursa, destinatia, clasa, valoare
FROM Clienti c, Bilete b
WHERE b.idclient = c.idclient AND c.statut = 'VIP';

The only primary key is nrbilet

CREATE OR REPLACE TRIGGER new_BiletNouVIP
  INSTEAD OF INSERT ON BiletNouVIP FOR EACH ROW
DECLARE
  rowcnt integer := 0;
BEGIN
  INSERT INTO Clienti(nume)
  VALUES(:new.nume);
  SELECT COUNT(*) INTO rowcnt FROM Bilete WHERE nrbilet = :new.nrbilet;
  IF rowcnt = 0 THEN    
    INSERT INTO Bilete(nrbilet, clasa, valoare, sursa, destinatia)
    VALUES (:new.nrbilet, :new.clasa, :new.valoare, :new.sursa, :new.destinatia);
  ELSE
    UPDATE Bilete
    SET Bilete.clasa =: new.clasa, Bilete.valoare =: new.valoare, Bilete.sursa =: new.sursa, Bilete.destinatia =: new.destinatia
    WHERE Bilete.nrbilet =: new.nrbilet;
  END IF;
END;`

Upvotes: 0

Views: 73

Answers (1)

APC
APC

Reputation: 146209

In the ELSE branch your assignments have a space after the colon - =: new which is invalid syntax. The colon needs to be part of the :new namespace, like this

UPDATE Bilete
    SET Bilete.clasa = :new.clasa, 
        Bilete.valoare = :new.valoare, 
        Bilete.sursa = :new.sursa, 
        Bilete.destinatia = :new.destinatia
WHERE Bilete.nrbilet = :new.nrbilet

Upvotes: 1

Related Questions