-1

I have three triggers in a SQL file generating an Oracle base. When i start the file, i have no message of "Trigger created" and it seems that execution is locked at the trigger creation (i have creation confirmation for everything before).

Can you help me ? Here are the triggers :

CREATE TRIGGER TrainInfo_nbDispo1e_update_tr
BEFORE UPDATE ON TrainInfo
FOR EACH ROW
BEGIN
   IF :NEW.nbDispo1e != :OLD.nbDispo1e
   THEN
       RAISE_APPLICATION_ERROR(-20101, 'Blahblahblah');
   END IF;
END;


CREATE TRIGGER TrainInfo_nbDispo2e_update_tr
BEFORE UPDATE ON TrainInfo
FOR EACH ROW
BEGIN
   IF :NEW.nbDispo2e != :OLD.nbDispo2e
   THEN
       RAISE_APPLICATION_ERROR(-20101, 'Blahblahblah');
   END IF;
END;



CREATE TRIGGER Trajet_Distance_update_tr
BEFORE UPDATE ON Trajets
FOR EACH ROW
BEGIN
   IF :NEW.distance != :OLD.distance
   THEN
       RAISE_APPLICATION_ERROR(-20101, 'Blahblahblah');
   END IF;
END;
Csi
  • 526
  • 3
  • 22
  • 2
    Put a forward slash after the end of each create trigger statement. – David Aldridge May 25 '15 at 09:30
  • @David Aldridge That works, but why ? – Csi May 25 '15 at 09:32
  • If you are using SQL*Plus, a `/` on a line by itself tells the client to execute what is currently in the buffer (whatever code has been read up to that time). Otherwise, it has no idea where you intend your `CREATE TRIGGER` statement to end. – Justin Cave May 25 '15 at 09:34
  • 2
    http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch4.htm#i1039316 – David Aldridge May 25 '15 at 09:38
  • @Justin Cave So if i have an insert into statement that fails with error SQL Command not properly ended, it's because of a missing / ? – Csi May 25 '15 at 09:38
  • @Csi - It's certainly possible. Depending on exactly what you're doing. – Justin Cave May 25 '15 at 09:44
  • @Justin Cave Inserting multiple rows. Like insert into blob (thing, thingy) values (1,2), (2,3),(3,4); – Csi May 25 '15 at 09:48
  • 1
    Regarding the why: http://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695 –  May 25 '15 at 09:50
  • @Csi - That's not valid syntax. You can only have one set of values in a `values` clause. You'd need three `insert values` statements to insert 3 rows. Or one `insert select` where the `select` returns 3 rows. – Justin Cave May 25 '15 at 09:55

1 Answers1

0

Put / after every END; statement.

When you put / it tells compiler that execute sql statement present in buffer or above /

sujata
  • 16
  • 1