sequential trigger after re-boot? [message #36346] |
Mon, 19 November 2001 05:57 |
Tony
Messages: 190 Registered: June 2001
|
Senior Member |
|
|
I have a trigger that creates a sequential number in a column after an insert into another column. If I reboot my node, will the trigger maintain the sequential numbering, or return to 1? If the trigger does not maintain the sequential integrity, what can I do to maintain it in the event of a reboot? Thanks.
----------------------------------------------------------------------
|
|
|
Re: sequential trigger after re-boot? [message #36348 is a reply to message #36346] |
Mon, 19 November 2001 10:26 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
if you are using sequence number generators then they will not reset (you should be using them).
SQL> create sequence my_seq cache 20;
Sequence created.
SQL>
SQL> select sequence_name, cache_size, last_number from user_sequences;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
MY_SEQ 20 1
SQL>
SQL> select my_seq.nextval from dual;
NEXTVAL
----------
1
SQL>
SQL> select sequence_name, cache_size, last_number from user_sequences;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
MY_SEQ 20 21
SQL>
SQL> select my_seq.nextval from dual;
NEXTVAL
----------
2
SQL>
If oracle is restarted, you loose the cached values.
Also, when using the sequence in your trigger you
don't have to select in into a variable first,
just use it in the sql.
----------------------------------------------------------------------
|
|
|
Re: sequential trigger after re-boot? [message #36354 is a reply to message #36346] |
Mon, 19 November 2001 11:33 |
Tony
Messages: 190 Registered: June 2001
|
Senior Member |
|
|
Ok, how can this sequence number generator populate another column in a table? As opposed to making entries into user_sequences? Where could I place it in the below trigger? Also, I know why :new.medi_mrn does not advance beyond 100000001, but I'm not sure how to modify the value of :new.medi_mrn to equal the previous value, so I can add 1 to it. Thanks.
create or replace trigger my_mrn
before insert or update of MRN on MRN_DEASSOC
for each row
begin
:new.MEDI_MRN := 100000000;
if inserting then
if :new.MRN is not null then
:new.MEDI_MRN := :new.MEDI_MRN + 1;
end if;
end if;
end;
/
----------------------------------------------------------------------
|
|
|