1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
|
CREATE OR REPLACE TRIGGER TAGE_Ins
before delete on TAGS
declare
wMAT_Doublon varchar2(14);
wCount number(4);
wCount1 number(4);
i number(4);
begin
select count(*) into wCount from TAGS;
if wCount>1 then
select count(*)into wCount1 from TAGS where TAGS.AGS_C_MAT_AGENT in(select TAGS.AGS_C_MAT_AGENT from tags group by AGS_C_MAT_AGENT having count(AGS_C_MAT_AGENT)>1);
end if;
if wCount1 > 1 then
i:=1;
while (i<=wCount1) loop
if i>1 then
SELECT max(TAGS2.AGS_C_MAT_AGENT) into wMAT_Doublon
FROM TAGS2 where TAGS2.AGS_C_MAT_AGENT in(select TAGS2.AGS_C_MAT_AGENT from tags2 group by AGS_C_MAT_AGENT having count(AGS_C_MAT_AGENT)>1);
else
SELECT max(TAGS.AGS_C_MAT_AGENT) into wMAT_Doublon
FROM TAGS where TAGS.AGS_C_MAT_AGENT in(select TAGS.AGS_C_MAT_AGENT from tags group by AGS_C_MAT_AGENT having count(AGS_C_MAT_AGENT)>1);
end if;
DBMS_OUTPUT.ENABLE( 1000000 ) ;
DBMS_OUTPUT.PUT_LINE( ' ' || 'mat doublon ' || wMAT_Doublon) ;
DBMS_OUTPUT.PUT_LINE( ' ' || 'wCount1 ' || wCount1) ;
DBMS_OUTPUT.PUT_LINE( ' ' || 'i ' || i) ;
insert into TAGE (AGE_C_MAT_AGENT,AGE_C_CODE_EH,AGE_C_DATE_PAIE,AGE_C_CODE_LIGNE)select AGS_C_MAT_AGENT,AGS_C_CODE_EH,AGS_C_DATE_PAIE,
AGS_C_CODE_LIGNE from TAGS where TAGS.AGS_C_MAT_AGENT <> wMAT_Doublon;
insert into TAGE (AGE_C_MAT_AGENT,AGE_C_CODE_EH,AGE_C_DATE_PAIE,AGE_C_CODE_LIGNE)select distinct AGS_C_MAT_AGENT,AGS_C_CODE_EH,AGS_C_DATE_PAIE,
AGS_C_CODE_LIGNE from TAGS where TAGS.AGS_C_MAT_AGENT = wMAT_Doublon;
i:=i+1;
create table tags2 as
select * from tags
minus
select * from tags where tags.AGS_C_MAT_AGENT = wMAT_Doublon;
end loop;
end if;
insert into TSTA (STA_AGE_C_MAT_AGENT,STA_L_NB_SECONDE,STA_L_NB_AGENT_SEC) select AGS_C_MAT_AGENT,AGS_L_NB_SECONDE,AGS_L_NB_AGENT_SECONDE from tags;
end; |
Partager