Menu de navegação

[gsmdfParticipante]

Minha situação é de vinculação de um órgão a um órgão vinculador, uma tabela basicamente de histórico disso:

Tenho diversos órgãos.
Na tabela há o COD_ÓRGÃO_VINCULADO e COD_ORGAO_VINCULADOR,
Há informação de DT_INICIO e DT_FIM (esta pode ser null pois quer dizer que o órgão ainda está vinculado).
Um órgão,durante um período de tempo só pode estar vinculado a um órgão vinculador apenas.
Então quer dizer que, se o órgão X tiver DT_INICIO de vinculação a um órgão vinculador e DT_FIM null não pode haver outro registro nesta tabela com COD_ORGAO_VINCULADO X.
Se a DT_FIM for DD/MM/AAAA então não pode haver outro registro com COD_ORGAO_VINCULADO X que tenha DT_INICIO no período entre DT_INICIO e DT_FIM do outro registro.
Ficou claro?

Minha dúvida é: como ficaria o PL/SQL do corpo de uma trigger before insert e update que cheque se não há conflito de data com os registros já existentes na tabela, se houver conflito eu simplesmente levantaria um erro e mostraria uma mensagem.

[burgaParticipante]

Se você quer consultar os registros de uma tabela que está sendo alterada, você terá que fazer o esquema das 3 triggers, pra contornar o problema de tabela mutante.

Se procurar no fórum, você vai achar como resolver isto.

Agora, depois de feito isto, é só montar umas consultas de verificação com DT_INICIO e COD_ORGAO_VINCULADO, do mesmo jeito que você explicou no tópico.

Se a sua dúvida é em como montar as consultas, fala que a gente ajuda.

[fsitjaParticipante]

Uma forma de implementar:

---


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as FSITJA

SQL>
SQL> create table hist_vinculo (
2 COD_ORGAO_VINCULADO number(8) not null,
3 COD_ORGAO_VINCULADOR number(8) not null,
4 DT_INICIO date not null,
5 DT_FIM date,
6 primary key (COD_ORGAO_VINCULADO,
7 COD_ORGAO_VINCULADOR,
8 DT_INICIO));

Table created
SQL> alter table hist_vinculo add constraint ck_dt_fim check (dt_fim >= dt_inicio);

Table altered
SQL> CREATE OR REPLACE PACKAGE pack_hist_vinc IS
2 TYPE t_hist_vinculo IS TABLE OF hist_vinculo%ROWTYPE;
3 tab_hist t_hist_vinculo := t_hist_vinculo(NULL);
4 END;
5 /

Package created
SQL> create or replace trigger t_valida_hist_b_d
2 before insert or update on hist_vinculo
3 BEGIN
4 pack_hist_vinc.tab_hist.delete;
5 END;
6 /

Trigger created
SQL> create or replace trigger t_valida_hist_br_iu
2 after insert or update on hist_vinculo for each row
3 DECLARE
4 i NUMBER;
5 BEGIN
6 pack_hist_vinc.tab_hist.extend;
7 i := pack_hist_vinc.tab_hist.last;
8 pack_hist_vinc.tab_hist(i).cod_orgao_vinculado := :new.cod_orgao_vinculado;
9 pack_hist_vinc.tab_hist(i).cod_orgao_vinculador := :new.cod_orgao_vinculador;
10 pack_hist_vinc.tab_hist(i).dt_inicio := :new.dt_inicio;
11 END;
12 /

Trigger created
SQL> create or replace trigger t_valida_hist_a_iu
2 after insert or update on hist_vinculo
3 DECLARE
4 cont_dt_fim NUMBER;
5 BEGIN
6 FOR i IN 1 .. pack_hist_vinc.tab_hist.count
7 LOOP
8 SELECT COUNT()
9 INTO cont_dt_fim
10 FROM hist_vinculo
11 WHERE cod_orgao_vinculado = pack_hist_vinc.tab_hist(i).cod_orgao_vinculado
12 AND cod_orgao_vinculador = pack_hist_vinc.tab_hist(i).cod_orgao_vinculador
13 AND dt_fim IS NULL;
14 IF cont_dt_fim > 1
15 THEN
16 raise_application_error(-20000,
17 'Não pode haver mais de um vínculo aberto para COD_ORGAO_VINCULADO ' || pack_hist_vinc.tab_hist(i)
18 .cod_orgao_vinculado || ' e COD_ORGAO_VINCULADOR ' || pack_hist_vinc.tab_hist(i)
19 .cod_orgao_vinculador);
20 END IF;
21 SELECT COUNT()
22 INTO cont_dt_fim
23 FROM (SELECT cod_orgao_vinculado,
24 cod_orgao_vinculador,
25 dt_inicio,
26 dt_fim,
27 lead(dt_inicio) over(PARTITION BY cod_orgao_vinculado, cod_orgao_vinculador ORDER BY dt_inicio) prox_dt_ini
28 FROM hist_vinculo)
29 WHERE prox_dt_ini 0
31 THEN
32 raise_application_error(-20001, 'Data Fim sobrepõe com a próxima Data de Início');
33 END IF;
34 END LOOP;
35 END;
36 /

Trigger created
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/01/2009', 'DD/MM/YYYY'), to_date('31/01/2009', 'DD/MM/YYYY'));

1 row inserted
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/02/2009', 'DD/MM/YYYY'), to_date('31/03/2009', 'DD/MM/YYYY'));

1 row inserted
SQL> -- testa sobreposição de períodos
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('30/03/2009', 'DD/MM/YYYY'), to_date('31/05/2009', 'DD/MM/YYYY'));

INSERT INTO hist_vinculo
(cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
VALUES
(1, 2, to_date('30/03/2009', 'DD/MM/YYYY'), to_date('31/05/2009', 'DD/MM/YYYY'))

ORA-20001: Data Fim sobrepõe com a próxima Data de Início
ORA-06512: at "FSITJA.T_VALIDA_HIST_A_IU", line 30
ORA-04088: error during execution of trigger 'FSITJA.T_VALIDA_HIST_A_IU'
SQL> -- testa deixar 2 datas fim abertas
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/04/2009', 'DD/MM/YYYY'), null);

1 row inserted
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/05/2009', 'DD/MM/YYYY'), null);

INSERT INTO hist_vinculo
(cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
VALUES
(1, 2, to_date('01/05/2009', 'DD/MM/YYYY'), null)

ORA-20000: Não pode haver mais de um vínculo aberto para COD_ORGAO_VINCULADO 1 e COD_ORGAO_VINCULADOR 2
ORA-06512: at "FSITJA.T_VALIDA_HIST_A_IU", line 14
ORA-04088: error during execution of trigger 'FSITJA.T_VALIDA_HIST_A_IU'

SQL>


[gsmdfParticipante]

Perfeito, você poderia me explicar resumidamente o que é o PACKAGE que você usou ali no começo e a utilidade dele na solução apresentada?

Vlw!

[fsitjaParticipante]

A package é um local temporário para armazenar as linhas que estão sendo modificadas pelas triggers for each row. Como na trigger for each row você não pode dar select na tabela pois ela está, digamos assim, “em manutenção”, você precisa de um lugar para armazenar quais linhas foram inseridas/atualizadas para que a trigger after statement possa usá-las no where e verificar se elas não violaram sua regra de consistência.

Outro dia desses escrevi sobre o assunto num outro forum, se você quiser ler, está explicado mais metaforicamente 😆

http://glufke.net/oracle/viewtopic.php? … c&start=20

[gsmdfParticipante]

Ahh, entendi pelo post que você explicou mais metaforicamente.
Muito obrigado, realmente isso de tabela mutante é um problema.

[gsmdfParticipante]

Fsitja, duas dúvidas.
1) O que são esses comandos que você usou no package?

pack_hist_vinc.tab_hist.last;
pack_hist_vinc.tab_hist.count
pack_hist_vinc.tab_hist.extend;

Teria algum link para eu ver e estudar esses comandos?

2) O que são os comandos LEAD, over , partition by usados abaixo?Poderia me explicar esse select?

SELECT COUNT(*)
22 INTO cont_dt_fim
23 FROM (SELECT cod_orgao_vinculado,
24 cod_orgao_vinculador,
25 dt_inicio,
26 dt_fim,
27 lead(dt_inicio) over(PARTITION BY cod_orgao_vinculado, cod_orgao_vinculador ORDER BY dt_inicio) prox_dt_ini
28 FROM hist_vinculo)
29 WHERE prox_dt_ini 0
31 THEN
32 raise_application_error(-20001, 'Data Fim sobrepõe com a próxima Data de Início');

Obrigado, implementei essa solução aqui no BD e funcionou corretamente.

[fsitjaParticipante]

Last, Count e Extend são métodos para manipulação de collections (tabelas em memória).

A bibliografia para estudar fica aqui:
http://www.oracle.com/pls/db112/to_URL? … LNPLS00508

Lead é uma função analítica, que serve para ler outras linhas fora da linha atual (num único scan da tabela), no caso a próxima linha dada por uma cláusula ORDER BY, dentro de uma partição lógica. Eu recomendo testar e ver exemplos para entender. Dê uma olhada no link abaixo e pesquise na internet para mais detalhes.

http://download.oracle.com/docs/cd/E118 … ons004.htm

[Autor]

Posts