Forum Stats

  • 3,724,456 Users
  • 2,244,761 Discussions
  • 7,851,018 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sequences and Triggers

708699
708699 Member Posts: 69
edited August 2009 in APEX Discussions
I'm feeling awfully dumb, as I can't tackle yet another beginner problem, nor can I find any place with good information.

I want to create a sequence that is the primary key of a table, that starts at 1 and increments by 1. For every time I insert a row in the table I want it to trigger this sequence in the primary key. I.e, I want to insert movie names, and I want the number of movies to be the Pk. (I.e. count from 1 up per movie).

I would think you would need a Sequence, and a Trigger. The Sequence being set as the primary key when you create the table.
I have done all of this. Created a Table with the primary key of the sequence that I created before hand. Then I tried to create a trigger to fire off before every insert.

Here is the SQL for the Sequence
 CREATE SEQUENCE   "MOVIE_NUMBER"  MINVALUE 1 MAXVALUE 200 INCREMENT BY 1 START WITH 9 NOCACHE  NOORDER  NOCYCLE
And here is the SQL for my Trigger
CREATE OR REPLACE TRIGGER  "BI_MOVIES" 
  before insert on "MOVIES"               
  for each row  
begin   
    select "MOVIE_NUMBER".nextval into :NEW."MOVIE_NUMBER" from dual;  
end; 
Here is my Table SQL
CREATE TABLE  "MOVIES" 
   (	"MOVIE_NUMBER" NUMBER(28,100) NOT NULL ENABLE, 
	"QUALITY" VARCHAR2(10), 
	"PRODUCER" VARCHAR2(80), 
	"YEAR_PRODUCED" NUMBER(1,5), 
	"DESCRIPTION" VARCHAR2(4000), 
	 CONSTRAINT "MOVIES_PK" PRIMARY KEY ("MOVIE_NUMBER") ENABLE
   )
/


CREATE OR REPLACE TRIGGER  "BI_MOVIES" 
  before insert on "MOVIES"               
  for each row  
begin   
    select "MOVIE_NUMBER".nextval into :NEW."MOVIE_NUMBER" from dual;  
end; 
If I enter a number into the primary key "MOVIE_NUMBER" (through the APEX GUI Object Browser), then it gives me
error ORA-01438: value larger than specified precision allowed for this column
If I don't enter any number into "MOVIE_NUMBER", then I get
error ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "MOVIE_LIST.BI_MOVIES", line 2 ORA-04088: error during execution of trigger 'MOVIE_LIST.BI_MOVIES'
MOVIE_NUMBER datatype is NUMBER(28,100), so I find it hard that it is larger than the specified precision? I don't know what is going on or why it is not working.
Any help would be greatly appreciated.

Thank you,
Tom
Tagged:

Best Answer

  • Sc0tt
    Sc0tt Member Posts: 1,708
    Accepted Answer
    If its just a primary key, make it a number and dont give a precision. A sequence is just a unique number and if its your PK you shouldnt really care what the number is.

    If movie_number is some unique identifier, create a unique constraint on that column with whatever precision you need.

Answers

  • Sc0tt
    Sc0tt Member Posts: 1,708
    Accepted Answer
    If its just a primary key, make it a number and dont give a precision. A sequence is just a unique number and if its your PK you shouldnt really care what the number is.

    If movie_number is some unique identifier, create a unique constraint on that column with whatever precision you need.
  • 708699
    708699 Member Posts: 69
    Hah, I didn't know you could do that.

    It works like a charm.

    Might you possibly know how I could make it so that I don't see that Column in the Object Browser in APEX? Right now I see it when I enter information, but it doesn't matter what I enter, it inserts the right sequence value.

    It's just annoying to see it there.

    Thank you much,
    Tom
  • Sc0tt
    Sc0tt Member Posts: 1,708
    If you were to create a new record using Insert Row I dont think you can remove it - if you were to hit the Query button, you can check the fields you want to see and remove it that way.

    Other than that you have limited control in the Object Browser. But when you go to create a real form over the table, you probably would choose to not show the sequenced PK to the user on your form and just let your trigger do its magic without the using knowing the gory details :)
    Sc0tt
  • 708699
    708699 Member Posts: 69
    Figured as much,

    Thank you again,
    Tom
  • ATD
    ATD Member Posts: 11,184
    Hi,

    >
    MOVIE_NUMBER datatype is NUMBER(28,100),
    >

    Check the NUMBER datatype here: [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref84]

    NUMBER(p, s) = p digits with s of those digits after the decimal place (if s is greater than p, then p is padded with leading zeroes to make s digits in total after the decimal). So NUMBER(3,2) is not 123.45 but 1.23. So NUMBER(28,100) is 0.00000.....00123 (a total of 100 digits after the decimal) and 1 is higher than this, hence the error

    Andy
    ATD
  • 708699
    708699 Member Posts: 69
    I had it backwards then -

    Goodness Gracious, I do not know what I am doing in APEX yet.

    Even finished the 2 day guide, and the advanced guide.

    I guess I just need to read more carefully.
  • ATD
    ATD Member Posts: 11,184
    Don't worry, I'm sure you'll get there!

    Andy
This discussion has been closed.