CODEWITHSUNDEEP
oracle-databaseplsqltriggers
CDnX
CDnX

Reputation: 391

Oracle DB Trigger Syntax

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.

Upvotes: 0

Views: 57

Answers (1)

APC
APC

Reputation: 146239

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!)

Upvotes: 1

Related Questions