Generate sequential number to each transaction

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
  

Poston Fri, 10 Jun 2011 4:22 pm

Hello everyone, I have the following questions, presiso generate a sequential number for the field NR_SEQ each new transaction.

structure of table TABELA_TRANSACAO CD_TRANSACAO PK NR_SEQ PFC ...
davidcastilholi
Location: Jussara PR

David

Poston Fri, 10 Jun 2011 4:33 pm

you have to create a sequence and a trigger for your table
Code: Select all

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

Code: 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;

victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Fri, 10 Jun 2011 5:05 pm

I need that works as follows: for each new transaction sequence number zero and start a new count: transaction code-CD_TRANSACAO = 1-transaction items NR_SEQ = 1 NR_SEQ = 2 NR_SEQ = 3 CD_TRANSACAO = Code of transaction-transaction Items 2-NR_SEQ = 1 NR_SEQ = 2 NR_SEQ = 3 CD_TRANSACAO =-transaction Code 3 Items of the transaction-NR_SEQ = 1 NR_SEQ = 2 NR_SEQ = 3
davidcastilholi
Location: Jussara PR

David

Poston Mon, 13 Jun 2011 10:15 am

any ideas, suggestions?
davidcastilholi
Location: Jussara PR

David

Poston Mon, 13 Jun 2011 10:35 am

Dude, what is your reality?

you have a multi-block record? You can do a get on the blocks and get the current_record, which is the record sequential, or if it isn't a multi record, and is comitado row by row, you can make a query that returns a sequential by CD_TRANSACAO, anything put there!!

vlw
ederphil
Location: taquara-RS

Poston Mon, 13 Jun 2011 11:37 am

Keep doing a select every time on every table to see MAX might be slow one day, can think of recording the last generated sequential on a table.
diegolenhardt
Location: Recife

Poston Mon, 13 Jun 2011 12:48 pm

for sure, do a \"MAX + 1\" is not a good practice, but everything depends on the situation, in this case from what I understand he will have a number of items for each transaction, you'd have to have a number of representative items to be noticed the slowness, being that he would have to get the sequence of the transaction and not on the table, but everything depends on the case, vlw!
ederphil
Location: taquara-RS

Poston Mon, 13 Jun 2011 3:42 pm

Yes, max is only bad if you have a zillion lines for each code.
Normally this does not occur in a structure like this.

Still, you have to be careful with the MAX in case of 2 lines enter at the same time.
dr_gori
Location: Seattle, WA, USA

Thomas F. G
https://www.patreon.com/glufke

Poston Mon, 13 Jun 2011 4:04 pm

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

How am I supposed to do to get the get on the block and pick up the current_record, which is the record sequential? as our friend ederphil commented.
davidcastilholi
Location: Jussara PR

David

Poston Mon, 13 Jun 2011 4:15 pm

Get_Block_Property (' NOME_DO_BLOCO ', CURRENT_RECORD);
You can use the when-new-item-instance or when-create-record, good, dai depends on how you implement in your code!

vlw ...

qlqr thing on there!
ederphil
Location: taquara-RS

Poston Thu, 16 Jun 2011 9:37 am

guys sorry for the delay, I'm trying to do as follows: Using the trigger-WHEN-CREATE-RECORD
Code: 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 every time I do the process he plays 1 for decades on record field NR_SEQ what am I doing wrong?
davidcastilholi
Location: Jussara PR

David

Poston Thu, 16 Jun 2011 10:02 am

To work out the sequence you would have to throw up row by row, because he seeks the max of the Bank, which, if you do not comitou the previous record he will always be the same,
Code: Select all
BEGIN
   :TB_ITEMTRANSACAO.NR_SEQ := Get_Block_Property( 'TB_ITEMTRANSACAO, CURRENT_RECORD);
END;
ederphil
Location: taquara-RS

Poston Thu, 16 Jun 2011 12:56 pm

It worked ederphil, right, show de bola.


valeu
davidcastilholi
Location: Jussara PR

David

Poston Fri, 15 May 2015 9:18 am

taking advantage of the tip of ederphil how to update the sequence of a multi block record if for example, deleting a record from the middle of the sequence, such as reorder sequencing?

I tried some alternatives of walking the mile with loop to redo the sequel, but I'm not knowing which trigger use, some are restricted and do not allow:(
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Tue, 15 Nov 2016 10:08 am

Hello, good day. need to do the same, enter a sequence to the next record, in my case this would be sequential CONCIERGE, NR then wanted to QO form already open with this nr filled ... but, as I have only 1 month in apex5, I don't know where I put the code to work, could you help me?
carlynhos77



Return to Forms

Who is online

Users browsing this forum: Bing [Bot] and 2 guests