Home » SQL & PL/SQL » SQL & PL/SQL » sequential trigger after re-boot?
sequential trigger after re-boot? [message #36346] Mon, 19 November 2001 05:57 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;
/

----------------------------------------------------------------------
Previous Topic: procedures parameters
Next Topic: Re: How to execute a procedure stored in a database column?
Goto Forum:
  


Current Time: Mon Aug 03 05:06:28 CDT 2020