Oracle DB Trigger Syntax

Question

I tried to make primary key for table Staff automatically sequenced.

input:

CREATE SEQUENCE staff_idseq START WITH 1 INCREMENT BY 1 MAXVALUE 9999;
CREATE TABLE Staff
(
    staffid         number(4) not null primary key,
    name            varchar2(21) not null unique,
    permission      varchar2(10) not null
);
CREATE OR REPLACE TRIGGER staff_idtrig
    BEFORE INSERT ON Staff
    FOR EACH ROW WHEN (new.staffid is null)
    BEGIN
        SELECT staff_idseq.nextval INTO :new.staffid FROM dual;
    END;

but when I load this, after creating sequence and table, it requests me to input more command as if I forgot to add semicolon on end of line. Maybe there are some error on my query, but I cannot find what mistake was I made, because I failed to get out from input prompt without using Ctrl+c.

If you are on Oracle 12.1 or later you can define staffid number(4) generated as identity or set a sequence as a default value. — William Robertson
– William Robertson, Commented May 31, 2017 at 18:15

APC · Accepted Answer · 2017-05-31 12:41:04Z

1

Triggers are PL/SQL programs, which use a semi-colon as a line terminator. So to execute a create or replace statement we need to finish the code with a forward slash on a new line.

Try this:

CREATE OR REPLACE TRIGGER staff_idtrig
    BEFORE INSERT ON Staff
    FOR EACH ROW WHEN (new.staffid is null)
BEGIN
    SELECT staff_idseq.nextval INTO :new.staffid FROM dual;
END;
/

As @a_horse_with_no_name says, if you're on 11g why not use this simpler syntax:

:new.staffid := staff_idseq.nextval;

Under the covers Oracle still executes a select on dual, it's just syntactic sugar (and less typing!)

edited May 31, 2017 at 12:41

answered May 31, 2017 at 12:16

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Or simpler: :new.staffid := staff_idseq.nextval;

Collectives™ on Stack Overflow

Oracle DB Trigger Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related