creating insert trigger error

Question

i'm trying to create a trigger that sets the id from a predefined sequence.

CREATE SEQUENCE seq_list_status 
    START WITH 1 
    INCREMENT BY 1 

;

CREATE OR REPLACE TRIGGER trg_list_status_insert 
    BEFORE INSERT ON list_status 
    FOR EACH ROW 
    select seq_list_status.nextval into :new.id from dual; 
/

i'm getting the error below while creating the trigger

Error starting at line 1 in command:
CREATE OR REPLACE TRIGGER trg_list_status_insert 
    BEFORE INSERT ON list_status 
    FOR EACH ROW 
    select seq_list_status.nextval into :new.id from dual
Error at Command Line:4 Column:4
Error report:
SQL Error: ORA-04079: invalid trigger specification
04079. 00000 -  "invalid trigger specification"
*Cause:    The create TRIGGER statement is invalid.
*Action:   Check the statement for correct syntax.

i googled it but it seems all ok. any idea for what could be wrong?

Answer 1

Triggers are program units. Consequently we must wrap the code body in BEGIN and END. Try this

CREATE OR REPLACE TRIGGER trg_list_status_insert
    BEFORE INSERT ON list_status
    FOR EACH ROW
BEGIN
    select seq_list_status.nextval into :new.id from dual;
END;
/

Unfortunately the examples in the SQL Reference are as helpful as we would like. But it does link to other useful documents, such as the App Developers Guide.

Answer 2

you are missing the begin end

CREATE OR REPLACE TRIGGER trg_list_status_insert 
BEFORE INSERT ON list_status 
FOR EACH ROW 
BEGIN
select seq_list_status.nextval into :new.id from dual; 
END;
/