[ORACLE] Implementatie constraint en/of trigger

[Verwijderd]

Ik probeer het hier even omdat op de oracle forums en op metalink niet veel te vinden is.

Hier de vraag:

DE applicatie draait in webforms (6i) op een 8.1.7 database server.
We willen een aantal business rules op de database implementeren. Het afgeslankte voorbeeld is het volgende: Indien een verkoper een type bezoek VISIT bij een verkooppunt heeft en hij boekt dat in als DAY, dan kan hij op die dag niets meer inplannen als AM/PM.
Geen probleem, dit heb ik met een after insert/update trigger op row level opgevangen en een after insert/update op statement level (Om mutating triggers te vermijden).
Nu komt het probleem, omdat het verkopers zijn en dus niets van de eerste maal correct kunnen ingeven Komt soms de volgende situatie voor:
Ze plaasten een VISIT op AM en ander op PM, dat wordt goed gecommit op de database, de rule is niet geschonden. Maar dan komen ze tot de conclusie dat de de combinatie AM PM eigenlijk DAY is en willen ze deze twee VISITS Naar DAY zetten. Op de database wordt bij validatie via de trigger echter voor het tweede record nog PM gevonden en de combinatie PM/DAY mag niet met als gevolg dat de enige mogelijkheid is dat ze een van de twee records moeten deleten, commiten het ene record wijzigen en dan het tweede record opnieuw toevoegen.

Ik zou graag een oplossing vinden zodat de check pas afgaat als beide records naar de database zijn gestuurt, want dat worden de business rules niet geschonden. Constraints zijn ook geen oplossing want het is niet toegestaan om select staments in constraint te gebruiken.

Ben ik wat duidelijk Want ik geraak meestal niet uit mijn woorden

De code van de before insert/update trigger op elk record.

code:

 CREATE OR REPLACE TRIGGER before_check_int_cal
BEFORE INSERT  OR UPDATE 
ON epos_internal_calendar
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
pck$epos.v_int_cal_entries := pck$epos.v_int_cal_entries + 1;
pck$epos.v_eic_pos_id(pck$epos.v_int_cal_entries) := :NEW.POS_ID;
pck$epos.v_eic_am_pm(pck$epos.v_int_cal_entries) := :NEW.AM_PM;
pck$epos.v_eic_icty_id(pck$epos.v_int_cal_entries) := :NEW.ICTY_ID;
pck$epos.v_eic_icst_id(pck$epos.v_int_cal_entries) := :NEW.ICST_ID;
pck$epos.v_eic_from_date(pck$epos.v_int_cal_entries) := :NEW.FROM_DATE;
pck$epos.v_eic_till_date(pck$epos.v_int_cal_entries) := :NEW.TILL_DATE;
pck$epos.v_eic_iper_id(pck$epos.v_int_cal_entries) := :NEW.IPER_ID;
pck$epos.v_eic_id(pck$epos.v_int_cal_entries) := :NEW.ID;
end;

De code van de before insert/update trigger per statement

code:

CREATE OR REPLACE TRIGGER after_check_int_cal
AFTER INSERT  OR UPDATE 
ON epos_internal_calendar
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
  v_pos_id      epos_internal_calendar.pos_id%type;
  v_am_pm       epos_internal_calendar.am_pm%type;
  v_icty_id     epos_internal_calendar.iper_id%type;
  v_test        Number;
  v_icst_id     epos_internal_calendar.icst_id%type;
  v_from_date   epos_internal_calendar.from_date%type;
  v_till_date   epos_internal_calendar.till_date%type;
  v_iper_id     epos_internal_calendar.iper_id%type;
  v_id     epos_internal_calendar.id%type;

  cursor get_ical_type_id (p_type epos_int_cal_types.type%type)
  is
  select id from epos_int_cal_types
  where type = p_type;

  r_ical_type_id get_ical_type_id%rowtype;

  cursor check_period(p_iper_id epos_internal_calendar.iper_id%type,p_id epos_internal_calendar.id%type) IS
    select epos_internal_calendar.icty_id,
           epos_internal_calendar.from_date,
            epos_internal_calendar.till_date,
            epos_internal_calendar.am_pm
         FROM epos_internal_calendar
         where am_pm = 'P'
         AND iper_id = p_iper_id 
          and id != p_id;  


 cursor check_all(p_from_date epos_internal_calendar.from_date%type, p_till_date epos_internal_calendar.till_date%type,p_iper_id epos_internal_calendar.iper_id%type,p_id epos_internal_calendar.id%type )IS
    select epos_internal_calendar.icty_id,
           epos_internal_calendar.from_date,
            epos_internal_calendar.till_date,
            epos_internal_calendar.am_pm
            from epos_internal_calendar
            where (From_date >= p_FROM_DATE and from_date <= p_TILL_DATE) or (till_date >= p_FROM_DATE and till_date <= p_TILL_DATE)
            AND IPER_ID = p_iper_id 
             and id != p_id;  

     
    cursor check_ampm_type(ampm_type epos_internal_calendar.am_pm%type,p_from_date epos_internal_calendar.from_date%type, p_till_date epos_internal_calendar.till_date%type,p_iper_id epos_internal_calendar.iper_id%type,p_id epos_internal_calendar.id%type) IS
    select epos_internal_calendar.icty_id,
           epos_internal_calendar.from_date,
            epos_internal_calendar.till_date,
            epos_internal_calendar.am_pm
            from epos_internal_calendar
            where From_date between p_FROM_DATE and p_TILL_DATE
            and am_pm in (ampm_type)
            AND IPER_ID = p_iper_id 
            and id != p_id;        

  r_check_all check_all%rowtype;
  r_check_period check_period%rowtype;
 
  r_check_ampm_type check_ampm_type%rowtype;
  go_on boolean;
  message_shown boolean;

BEGIN

 go_on := true;
 message_shown := false;


-- loop through the memory table defined in pck$epos to check if doubles exist
for i in 1..pck$epos.v_int_cal_entries loop
    v_pos_id := pck$epos.v_eic_pos_id(i);
    v_am_pm := pck$epos.v_eic_am_pm(i);
    v_icty_id := pck$epos.v_eic_icty_id(i);
    v_icst_id := pck$epos.v_eic_icst_id(i);
    v_from_date := pck$epos.v_eic_from_date(i);
     v_till_date := pck$epos.v_eic_till_date(i);
    v_iper_id := pck$epos.v_eic_iper_id(i);
      v_id := pck$epos.v_eic_id(i);
    -- the test to check for doubles
    select count(*) into v_test
    from epos_internal_calendar ical, epos_pos pos
    where pos_id = v_pos_id
    and pos.id = ical.pos_id
    and pos.salesnbr_amp not like 'NEW%'
    --and am_pm = v_am_pm  --NO POS CAN BE VISITED TWICE IN A DAY
    and icty_id = v_icty_id
    --and icst_id = v_icst_id --NO POS CAN BE VISITED TWICE IN A DAY
    and (
         (iper_id = v_iper_id) OR
         (iper_id != v_iper_id and v_icty_id in (select id from epos_int_cal_types where type='VISIT'))
        )
    and to_char(from_date,'YYYYMMDD') = to_char(v_from_date,'YYYYMMDD');

    if v_test >=2 then
       pck$messaging6.raise_error('EPOS-0020');
    end if;



    if v_AM_PM ='D'
     then  --Check if already something on AM or PM
            open check_ampm_type('AM',v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_ampm_type into r_check_ampm_type;
            
            if check_ampm_type%found
            then    close check_ampm_type;
                    pck$epos.v_int_cal_entries := 0;   
                    pck$messaging6.raise_error('EPOS-0024');
            else close check_ampm_type;        
            end if;   
            
            open check_ampm_type('PM',v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_ampm_type into r_check_ampm_type;
             
            if check_ampm_type%found
            then     close check_ampm_type;
                    pck$epos.v_int_cal_entries := 0;                
                    pck$messaging6.raise_error('EPOS-0024');
            else close check_ampm_type;
            end if;
            
            open check_ampm_type('D',v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_ampm_type into r_check_ampm_type;     
            if check_ampm_type%notfound
                then     close check_ampm_type;
                       
            else close check_ampm_type;
                            if  ((r_check_ampm_type.icty_id = 2 and v_icty_id != 2)or (r_check_ampm_type.icty_id != 2 and v_icty_id = 2) or (r_check_ampm_type.icty_id != 2 and v_icty_id !=2) )
                            then pck$epos.v_int_cal_entries := 0;
                                    pck$messaging6.raise_error('EPOS-0024');
                                 message_shown := true;
                            end if;
                       
            end if ;
      end if ;
  
      --Check if AMPM = AM

if v_AM_PM ='AM'
then    --Check if already something on DAY        
        open check_ampm_type('D',v_from_date,v_till_date,v_iper_id,v_id);
        fetch check_ampm_type into r_check_ampm_type;
            
        if check_ampm_type%found
            then    close check_ampm_type;
                    pck$epos.v_int_cal_entries := 0;        
                    pck$messaging6.raise_error('EPOS-0024');
            else close check_ampm_type;        
        end if; 

        --Check what is planned on type AM
            open check_ampm_type('AM',v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_ampm_type into r_check_ampm_type;     
            if check_ampm_type%notfound
                then     close check_ampm_type;
                         
            else close check_ampm_type;
                            if  ((r_check_ampm_type.icty_id = 2 and v_icty_id != 2)or (r_check_ampm_type.icty_id != 2 and v_icty_id = 2) or (r_check_ampm_type.icty_id != 2 and v_icty_id !=2) )
                            then pck$epos.v_int_cal_entries := 0;   
                                 pck$messaging6.raise_error('EPOS-0024');
                                 message_shown := true;
                            end if;
                       
            end if ;
end if;            


--Check if AMPM = PM
if v_AM_PM ='PM'
then    --Check if already something on DAY        
        open check_ampm_type('D',v_from_date,v_till_date,v_iper_id,v_id);
        fetch check_ampm_type into r_check_ampm_type;
            
        if check_ampm_type%found
            then    close check_ampm_type;
                    pck$epos.v_int_cal_entries := 0;   
                    pck$messaging6.raise_error('6');
            else close check_ampm_type;        
        end if; 

        --Check what is planned on type AM
            open check_ampm_type('PM',v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_ampm_type into r_check_ampm_type;     
            if check_ampm_type%notfound
                then     close check_ampm_type;
                         
            else close check_ampm_type;
                            if  ((r_check_ampm_type.icty_id = 2 and v_icty_id != 2)or (r_check_ampm_type.icty_id != 2 and v_icty_id = 2) or (r_check_ampm_type.icty_id != 2 and v_icty_id !=2) )
                            then pck$epos.v_int_cal_entries := 0;    
                                 pck$messaging6.raise_error('EPOS-0024');
                                 message_shown := true;
                            else
                                 --v_multiple := FALSE;
                                 null;
                            end if;
                       
            end if ;
end if;


if v_AM_PM ='P'
      then  open check_all(v_from_date,v_till_date,v_iper_id,v_id);
            fetch check_all into r_check_all;

            if check_all%found
            then    close check_all;
                    pck$epos.v_int_cal_entries := 0;   
                    pck$messaging6.raise_error('EPOS-0024');
                    message_shown := true;
            else close check_all;
            end if;

end if;


 --Check if there is something planned on this day, by use of Period


  if message_shown = false
  then  open check_period(v_iper_id,v_id);
        fetch check_period into r_check_period;

        if check_period%notfound
        then close check_period;

        else    while go_on LOOP
                     if v_FROM_DATE >= r_check_period.from_DATE and v_TILL_DATE <= r_check_period.TILL_DATE
                     then close check_period;
                     pck$epos.v_int_cal_entries := 0;
                         pck$messaging6.raise_error('EPOS-0024');

                          go_on := false;
                     else fetch check_period into r_check_period;
                            if check_period%notfound
                            then close check_period;
                                go_on:= false;
                            end if ;
                     end if;

                end loop;

        end if;
  end if;

end loop;


pck$epos.v_int_cal_entries := 0;
end;

[cameodski]

Ik weet niet erg veel van Oracle, maar is het geen optie om beide records tegelijk te inserten dmv een UNION. Je krijgt dan zoiets:

code:

INSERT table (veld1, veld2...)
SELECT waarde1, waarde2
UNION
SELECT waarde3, waarde4

[Verwijderd]

cameodski schreef op 04 november 2002 @ 16:32:
Ik weet niet erg veel van Oracle, maar is het geen optie om beide records tegelijk te inserten dmv een UNION. Je krijgt dan zoiets:

code:

INSERT table (veld1, veld2...)
SELECT waarde1, waarde2
UNION
SELECT waarde3, waarde4

Dit is geen oplossing, ik had het in het voorbeeld eenvoudiger voor gesteld dan het was. Het kan imers ook dat eer een record wordt ge-insert en een tweede geupdate, of dat ze drie records updaten etc. . Maar jou opmerking doet er mij wel aan denken dat ik vergeten ben te vermelden dat de records worden ge-insert/ge-update via webforms en dat die toch per record, voor een update/insert zorgt. (update via rowid)

[cameodski]

Ik denk dat je dan toch zelf zult moeten bepalen wanneer die check wel uitgevoerd moet worden. Dat kan een database niet voor jou weten.
Maar ik snap ook nog niet helemaal wat je bedoelt. Iemand voor een record toe met AM en eentje met PM. Vervolgens bedenkt ie dat ipv AM en PM beter DAY gebruikt had kunnen worden. Dan moet je dus één record verwijderen en de tweede updaten.
Je kunt ook via een of ander veld het geheel laten bevestigen oid en op basis daarvan de check gaan uitvoeren.

[Verwijderd]

cameodski schreef op 04 november 2002 @ 16:53:
Ik denk dat je dan toch zelf zult moeten bepalen wanneer die check wel uitgevoerd moet worden. Dat kan een database niet voor jou weten.
Maar ik snap ook nog niet helemaal wat je bedoelt. Iemand voor een record toe met AM en eentje met PM. Vervolgens bedenkt ie dat ipv AM en PM beter DAY gebruikt had kunnen worden. Dan moet je dus één record verwijderen en de tweede updaten.
Je kunt ook via een of ander veld het geheel laten bevestigen oid en op basis daarvan de check gaan uitvoeren.

Een Oracle database en waarschijnlijk nog een heleboel andere relationele databases checken de heletijd, validatie constraints, foreign key constraints, triggers etc. Dat is de kracht en ook de zwakte, het is niet zoals programeren in C waar ja een heleboel fouten opvang zlf moet voorzien, de database doet een heleboel voor jou en soms teveel.
Je hebt gelijk dat ik dat programatoris zou kunnen opvagen en zelf het tweede record deleten en weer aanmaken met de goede waarde. Maar dit is niet echt de bedoeling van een Oracle Database. Er zou en manier moeten zijn waarbij hij de validatie slechts doet als de twee records zijn verwerkt ik zal de code van de triggers plaatsen in mijn eerste post. Mischien wordt het dan wat duidelijker.

[cameodski]

Kun je de quote-tags even vervangen door code-tags. Dat maakt het al een heel stuk leesbaarder.

[justmental]

Je probleem zit niet in wat je gebruikt, maar waarschijnlijk in je eigen checks.
In de after statement zijn de records al gepost en kun je ze dus gewoon benaderen.

[Verwijderd]

justmental schreef op 04 november 2002 @ 19:03:
Je probleem zit niet in wat je gebruikt, maar waarschijnlijk in je eigen checks.
In de after statement zijn de records al gepost en kun je ze dus gewoon benaderen.

Ok, zal morgen effe testen met een deel van de code. Volgens jou moeten de gewijzigde records dus al kunnen worden geselecteerd op de db.

[Verwijderd]

Ik denk dat de records toch niet naar de database zijn gepost.
Ik heb de code ingekort tot slechts een test. Op het moment dat de validatie op de rule failed display ik het record dat ik met de cursor uit de database haal, dat record heeft dus nog de oude waarde. Dat doet mij vermoden dat er nog niets is gepost op het moment van de after update/insert trigger.

[justmental]

Verwijderd schreef op 05 november 2002 @ 16:10:
Ik denk dat de records toch niet naar de database zijn gepost.
Ik heb de code ingekort tot slechts een test. Op het moment dat de validatie op de rule failed display ik het record dat ik met de cursor uit de database haal, dat record heeft dus nog de oude waarde. Dat doet mij vermoden dat er nog niets is gepost op het moment van de after update/insert trigger.

code:

Verbonden met:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

SQL> create table temp (temp varchar2(1));

Tabel is aangemaakt.

SQL> create trigger tmp_trig after insert or update on temp
  2  declare
  3  v_count binary_integer;
  4  begin
  5  select count(*) into v_count from temp;
  6  dbms_output.put_line (to_char(v_count));
  7* end;
SQL> /

Trigger is aangemaakt.

SQL> set serverout on

SQL> insert into temp values ('a');
1

1 rij is aangemaakt.

SQL>

[Goodielover]

Kan je de trigger niet "on-commit" maken. Dan kunnen beide wijzigingen worden doorgevoerd en wordt alleen de totale nieuwe situatie gevalideerd.

Eigenlijk heb je en ontwerp fout erin zitten.
blijkbaar heb je klanten, afspraken en deelnemers aan de afspraak.
In je AM/PM vehaal heb je dus twee afspraken
bij je DAY verhaal heb je één afspraak. Het klopt dus dat je één van de twee eerst moet verwijderen en dan als deelnemer aan de naar DAY gewijzigde moet toevoegen.