Estamos com uma dúvida aqui no meu trabalho com relação a uma trigger.
Temos uma tabela chamada MANIFESTACAO, que possui uma sequence como chave primária (campo ID_MANIFESTACAO). Essa tabela também possui vários outros campos. Até agora, toda consulta para identificar cada manifestação é realizada através do campo ID_MANIFESTACAO.
Nosso cliente quer, agora, que a consulta seja feita através de outro código, que seja mais legível. Sugeriu, então, que cada manifestação, daqui por diante, ganhe um novo código que irá identificá-la. Esse código deve ser no formato AAAANNNNNNN, onde o AAAA é o ano corrente e os NNNNNNN é uma sequencia de números. Na virada de ano, a sequencia de números é zerada.
Exemplos desse novo código:
20140000001
20140000002
.
.
.
20140000325
.
.
.
20150000001
20150000002
Para cada registro guardado na tabela manifestação, é gerado um código que é o incremento do código anterior, exceto na virada do ano.
Precisamos criar um campo CODIGO (que será unique) na tabela MANIFESTACAO. Até aí, tudo bem. O problema que estou passando é justamente como gerar esse código.
Criamos uma trigger para a geração automática desses códigos. Criamos também uma tabela chamada GERACAO_CODIGO, que possui 2 campos, ANO e CONTADOR, para ajudar na criação dos códigos.
Essa tabela GERACAO_CODIGO terá apenas 2 campos, o campo ANO e o campo CONTADOR. Inicialmente, a tabela conterá apenas 1 registro (abaixo):
ANO CONTADOR
2014 0
Então, após o primeiro insert na tabela MANIFESTACAO, o campo CODIGO receberá o valor 20140000001 e a tabela GERACAO_CODIGO sofrerá um update, ficando assim:
ANO CONTADOR
2014 1
ANO CONTADOR
2014 2
ANO CONTADOR
2014 3
.
.
.
ANO CONTADOR
2014 1569
O código para essa trigger é o seguinte:
CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON MANIFESTACAO
FOR EACH ROW
DECLARE
P_ANO NUMBER(4);
P_CONTADOR NUMBER(11);
P_CODIGO NUMBER(11);
BEGIN
--recupera o ano da data do cadastro da manifestação
P_ANO := to_number(tochar(:NEW.DT_CADASTRO_MANIFESTACAO,'yyyy'));
--recupera o valor do campo contador e coloca o resultado na variável P_CONTADOR, já incrementando o seu valor. A variável P_CONTADOR ajudará a formar o campo CODIGO
SELECT NVL(CONTADOR + 1,1)
INTO P_CONTADOR
FROM GERACAO_CODIGO WHERE ANO = PANO;
--atualiza a tabela GERACAO_CODIGO. Se a variável P_CONTADOR for maior que 1, devo apenas atualizar o campo CONTADOR do ano correspondente com o seu novo valor, já incrementado
IF (P_CONTADOR > 1) THEN
UPDATE GERACAO_CODIGO
SET CONTADOR = P_CONTADOR
WHERE ANO = P_ANO;
ELSE --atualiza a tabela GERACAO_CODIGO. Se a variável P_CONTADOR for igual a 1, significa que houve virada de ano, então eu devo inserir um registro na tabela GERACAO_CODIGO
INSERT INTO GERACAO_CODIGO values(P_ANO,0);
END IF;
--gera o valor do campo CODIGO, a partir do ano da manifestação e da variável P_CONTADOR
PCODIGO := TO_NUMBER(PANO || LPAD(P_CONTADOR,7,0));
:NEW.CODIGO := PCODIGO;
END;
SELECT NVL(CONTADOR + 1,1)
INTO P_CONTADOR
FROM GERACAO_CODIGO WHERE ANO = PANO;
Não possuo experiência com locks de tabelas no Oracle, gostaria que, por favor, alguém com mais experiência me ajudasse a resolver este problema. Um colega em outro fórum, sugeriu que eu colocasse o seguinte trecho de código bem antes do último trecho de código citado acima:
LOCK TABLE geracao_codigo
IN EXCLUSIVE MODE
NOWAIT;
Antecipadamente agradeço a ajuda!