CODEWITHSUNDEEP
oracle-databasetriggers
koraytaylan
koraytaylan

Reputation: 893

creating insert trigger error

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?

Upvotes: 1

Views: 2474

Answers (2)

APC
APC

Reputation: 146349

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 not as helpful as we would like. But it does link to other useful documents, such as the App Developers Guide.

Upvotes: 6

Pentium10
Pentium10

Reputation: 208042

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;
/

Upvotes: 5

Related Questions