-2

Creating identity column by using sequences and triggers. But failed to run the script because of missing semi colon at the end 

create table slide1 ( id number(10),
subject varchar2(25),constraint pk_id PRIMARY KEY(id)
);

Create sequence  slide1_sequence start with 1
increment by 1
minvalue 1
maxvalue 10000;

create sequence id_seq;

CREATE OR REPLACE TRIGGER slide1_trigger 
BEFORE INSERT ON slide1
FOR EACH ROw

BEGIN
  SELECT slide1_seq.NEXTVAL
  INTO :new.id
  FROM   dual
close slide1;
END;
hemalp108
  • 1,209
  • 1
  • 15
  • 23
Mani Mozhi
  • 3
  • 11
  • 1
    What does `close slide1` do? – Thilo Sep 26 '16 at 06:52
  • You're missing a semicolon after the `create table` statement; you create two sequences, but use the sequence `slide1_sequence` that you never created; the `close slide1` part is unknown to me – Aleksej Sep 26 '16 at 07:03
  • 1
    You are missing a `/` after the `create trigger`: http://stackoverflow.com/a/10207695/330315 And the trigger is needlessly complicated. `:new.id := slide1_seq.NEXTVAL;` will do just fine. No need for a `select` and the `close` part is incorrect as Aleksej pointed out. –  Sep 26 '16 at 07:05

1 Answers1

1
    SQL> create table slide1 ( id number(10),subject varchar2(25),constraint pk_id PRIMARY KEY(id));
    Table created

    SQL> Create sequence  slide1_sequence start with 1 increment by 1 minvalue 1 maxvalue 10000;
    Sequence created

SQL> create sequence id_seq;
Sequence created

    SQL> CREATE OR REPLACE TRIGGER slide1_trigger
      2  BEFORE INSERT ON slide1
      3  FOR EACH ROw
      4  
      5  BEGIN
      6    SELECT slide1_sequence.NEXTVAL
      7    INTO :new.id
      8    FROM   dual;
      9  END;
     10  
     11  /
    Trigger created
Prashant Mishra
  • 619
  • 9
  • 25