Generate sequence number to each transaction

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Post Reply
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

Hello everyone, I have the following doubt, to generate a sequential number for the nr_seq field each new transaction.

Table structure

Select all

TABELA_TRANSACAO 
 
CD_TRANSACAO  PFK  
NR_SEQ               PK 
. 
. 
. 
. 
.
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

You have to create a sequence and a trigger for your table

Select all

 
 
CREATE SEQUENCE transacao_seq 
    MINVALUE 1 
    MAXVALUE 999999999999999999999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20; 
 

Select all

 
 
create or replace trigger TRG_TABELA_TRANSACAO 
  before insert on TABELA_TRANSACAO 
  for each row 
declare 
  -- local variables here 
begin 
 
  SELECT transacao_seq.nextval 
  INTO :new.ID 
  FROM dual; 
 
end TRG_TABELA_TRANSACAO; 
 
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

I need to work as follows:

For each new transaction the sequential number zere and begins a new count:

Select all

Código da transação - CD_TRANSACAO = 1 
Itens da transação    - NR_SEQ = 1 
                                      NR_SEQ = 2 
                                      NR_SEQ = 3 
 
Código da transação - CD_TRANSACAO = 2 
Itens da transação    - NR_SEQ = 1 
                                       NR_SEQ = 2 
                                       NR_SEQ = 3 
 
Código da transação - CD_TRANSACAO = 3 
Itens da transação    - NR_SEQ = 1 
                                      NR_SEQ = 2 
                                      NR_SEQ = 3
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

Any idea, suggestion?
ederphil
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Wed, 15 Oct 2008 4:05 pm
Location: taquara-RS

Dude,

What is your reality?

Do you have a multi-record block? You can make a get in the blocks and pick up the current_record,
that is the segerial of registration,
or if it is not a multi record, and is commanded registration, you can make a query that Return a Segendancy by CD_Transacao,

Anything posts there !!

vlw
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

To be doing a select each time in the whole table to see the max may be slow one day,

may think of recording the last sequential generated on a table ...
ederphil
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Wed, 15 Oct 2008 4:05 pm
Location: taquara-RS

sure, to make a "max + 1" is not a good practice, but everything depends on the situation,
in this case for what I understood it will have a number of items for each transaction,
would have to have a number of items representative so that the slowness is noticed,
being that he would have to seek the sequential transaction and not from the table,
but everything depends on the case,

vlw!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Yes, Max is only bad if you have a zillion of lines for each code.
Usually this does not occur in such a structure.

Even so, you have to be careful with Max in the case of 2 lines enter the same time.
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

The block I'm working on is multi-record.

How would I get to get the get in the block and get the current_record,
What is the registration segerial? As our friend Ederphil commented.
ederphil
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Wed, 15 Oct 2008 4:05 pm
Location: taquara-RS

Select all

Get_Block_Property( 'NOME_DO_BLOCO', CURRENT_RECORD);
You can use in the When-New Item-Instance or when-Create-Record, good, then depends on how you will implement in your code!

vlw ..

qlqr thing post there!
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

Sorry for the delay, I am trying to do as follows:

Using Trigger - When-Create-Record

Select all

DECLARE 
	   CURSOR CUR_INCREMENTO IS 
	      SELECT MAX(NVL(:TB_ITEMTRANSACAO.NR_SEQ,0)) + 1  
	      FROM TB_ITEMTRANSACAO WHERE CD_PRODUTO = :BLK_LANCAMENTO.CD_LANCAMENTO; 
BEGIN 
   OPEN CUR_INCREMENTO; 
      FETCH CUR_INCREMENTO INTO :TB_ITEMTRANSACAO.NR_SEQ; 
   CLOSE CUR_INCREMENTO; 
END;


But whenever I do the process he plays 1 for cadas record in the nr_seq field

What am I doing wrong?
ederphil
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Wed, 15 Oct 2008 4:05 pm
Location: taquara-RS

To work out the sequence you would have to start registration registration, as it seeks the Max of the bank, which, if you did not commit the previous record it will always be the same,

Select all

 
BEGIN  
   :TB_ITEMTRANSACAO.NR_SEQ := Get_Block_Property( 'TB_ITEMTRANSACAO, CURRENT_RECORD);  
END; 
davidcastilholi
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 47
Joined: Thu, 07 Apr 2011 3:54 pm
Location: Jussara PR
David

It's right Ederphil, right, ball show.


valeu
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Taking advantage of ederphil


How to upgrade the sequence of a multi block record if for example, delete a registry of the middle of the sequence, how to reorder sequencing?

I tried some alternatives to cross the block with loop to redo the sequence, but I do not know which trigger to use, some are restricted and do not allow :(
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 172
Joined: Mon, 24 Oct 2016 7:20 pm

Hello, good morning ... I need to do the same, insert a sequence to the next registration, in my case this sequential would be nr of concierge, so I wanted to open the form already opened with this nr filled ... but as I have only 1 month In Apex5, I do not know where I put the code to work, could you help me ??
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 19 guests