[SOLVED] Oracle: Statements running into each other

Oracle: Statements running into each other

I am not fluent with Oracle, and am testing out an auto-incrementing trigger required for older versions, where IDENTITY is not yet available.

CREATE TABLE stuff (
    id int PRIMARY KEY,
    data varchar(255)
);

CREATE SEQUENCE stuff_sequence;
CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;

INSERT INTO stuff(data) VALUES('test');

Using SQL Developer, I can run the CREATE TABLE statement, and then the CREATE SEQUENCE statement, but when I run the CREATE TRIGGER statement, it highlights the INSERT statement as well, and then complains about `Encountered the symbol "INSERT". Obviously, if I try to run the whole lot as a single script I get the same error.

What is happening here, and how do I fix it?

Solution

Just put / at the end of the trigger code. Which represents the end of the code above it.

CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;
/ -- this

INSERT INTO stuff(data) VALUES('test');

/ during the entering of a DML or DDL or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer" and / is needed in multiple lines code to indicate that the code is ending here.