[Oracle PL/SQL] trigger uitvoeren aan einde transaction

[Verwijderd]

Database omgeving: Oracle 10g.

Om het probleem te versimpelen heb ik dit voorbeeld gemaakt.

We hebben de volgende tabellen:

Automodel tabel

code:

id
name
description
datumToevoeging (= type datetime)

Onderdeel tabel

code:

id
name
description

Koppel tabel

code:

car_id
part_id

Relaties:
Automodel 1 ----- 0..* Koppel
Koppel * ---- 0..* Onderdeel

En het is de bedoeling dat er een nieuw automodel wordt toegevoegd met bijbehorende relaties met de onderdelen. Nadat deze is toegevoegd door een extern (closed source) programma moet een trigger gestart worden die de gegevens weer bij elkaar verzameld.

Met deze gegevens wordt een object in pl/sql gevuld. Dit object bevat dan alle gegevens van het automodel en dient te worden verzonden naar een webservice (dit is al werkend).

Het probleem is dat als ik 1 automodel met bijbehorende onderdelen bij elkaar wil verzamelen met een 'after insert' trigger op de 'Automodel' tabel de gegevens nog niet zijn weggeschreven naar de koppeltabel 'Koppel'. Hierdoor krijg ik een 'Automodel' dat in de trigger geen onderdelen bevat.

Nu heb ik voor dit probleem wat rond gezocht en de volgende mogelijke oplossingen bedacht:
• trigger laten 'sleepen' (ranzige + onbetrouwbare oplossing)
• trigger laten wachten tot het einde van de transaction (lijkt me de mooiste oplossing maar hoe? Gezocht maar niets gevonden)
• trigger vervangen door een procedure die hetzelfde werk verricht door periodiek uit gevoerd te worden (polling)
• trigger op de koppeltabel zetten en
• andere ideeën...?

Nu zou ik graag wat horen wat jullie van deze mogelijke oplossingen vinden. Als iemand een andere mogelijke oplossing heeft hoor ik dat ook graag.

[bazkar]

De trigger op de koppeltabel heeft als nadeel dat de trigger meerdere keren voor het zelfde automodel wordt afgetrapt (omdat er vast wel meerdere onderdelen aan de auto hangen).

Blijkbaar insert het closed source programma zijn gegevens NIET in een transactie (anders zou je trigger wel na de transactie plaats vinden en niet tijdens, dat is het idee van een transactie).

[JeroenTheStig]

Kun je in de tabel 'automodel' geen veld 'verwerkt_indicatie' toevoegen? Je kunt dan in de post-trigger van deze tabel een controle doen of deze op 'J' wordt gezet. Zo ja, stuur dan alle gegevens naar je object.

[Verwijderd]

bazkar schreef op donderdag 18 januari 2007 @ 09:23:
Blijkbaar insert het closed source programma zijn gegevens NIET in een transactie (anders zou je trigger wel na de transactie plaats vinden en niet tijdens, dat is het idee van een transactie).

Daar dacht ik ook aan. Echter vind ik nergens op internet dat het ook daadwerkelijk zo is. Heb je misschien een linkje?

Boktor schreef op donderdag 18 januari 2007 @ 09:35:
Kun je in de tabel 'automodel' geen veld 'verwerkt_indicatie' toevoegen? Je kunt dan in de post-trigger van deze tabel een controle doen of deze op 'J' wordt gezet. Zo ja, stuur dan alle gegevens naar je object.

Dat is geen een optie. Deze tabellen zijn van dat gesloten systeem en dienen niet aangepast te worden.

Verder heb ik iets gevonden over een 'statement trigger'. Als ik zo'n trigger op de koppel tabel zou zetten wordt er bij het wegschrijven van een x aantal onderdelen de trigger maar 1 keer gestart. Echter zie ik niet hoe ik aan de data kom die zijn toegevoegd omdat de :NEW variabelen alleen in 'row-level' triggers werken. Iemand hiermee een ervaringen?

[ Voor 3% gewijzigd door Verwijderd op 18-01-2007 15:00 ]

[Thyzz]

Verwijderd schreef op donderdag 18 januari 2007 @ 14:50:
Verder heb ik iets gevonden over een 'statement trigger'. Als ik zo'n trigger op de koppel tabel zou zetten wordt er bij het wegschrijven van een x aantal onderdelen de trigger maar 1 keer gestart. Echter zie ik niet hoe ik aan de data kom die zijn toegevoegd omdat de :NEW variabelen alleen in 'row-level' triggers werken. Iemand hiermee een ervaringen?

Wat je zou kunnen doen is een globale variabele (bv; array) definieren. Die variabele eerst legen met een global before trigger, daarna in een row-level trigger, die variabele vullen (hier heb je de :NEW), en dan in een global after trigger alles verwerken.

[The Fox NL]

Je kan denken aan deze constructie (pseudocode)

code:

CREATE OR REPLACE TYPE NumberList IS TABLE OF NUMBER;

CREATE OR REPLACE PACKAGE CarDinges
AS
  CarIds NumberList;
END;

CREATE OR REPLACE TRIGGER koppeltabel_gbi
BEFORE INSERT
    ON koppeltabel
BEGIN
    CarDinges.CarIds.Create;  --CarIds lijst leeg maken
END;

CREATE OR REPLACE TRIGGER koppeltabel_ai
AFTER INSERT
    ON koppeltabel
    FOR EACH ROW
DECLARE
  idx NUMBER; 
  found NUMBER;
BEGIN
  -- Kijk of Id niet al voorkomt
  found := 0;
  idx := CarDinges.AutoIds.FIRST;
  WHILE idx IS NOT NULL AND found = 0 LOOP
    IF CarDinges.CarIds(idx) = :NEW.car_id THEN
      found := 1;  -- je kan hier natuurlijk ook een RETURN gebruiken, dan heb je found variabele niet nodig, 't is maar wat je mooier vind
    END IF;
    idx := CarDinges.CarIds.NEXT(idx);
  END LOOP;

  IF found = 0 THEN
    CarDinges.CarIds.Extend; --maak collectie groter
    CarDinges.CarIds(CarDingest.CarIds.LAST) := :NEW.car_id; 
  END IF;
END;

CREATE OR REPLACE TRIGGER koppeltabel_gai
AFTER INSERT
    ON koppeltabel
DECLARE
  idx NUMBER;
BEGIN
  idx := CarDinges.CarIds.FIRST;
  WHILE idx IS NOT NULL AND found = 0 LOOP
    IF CarDinges.CarIds(idx) = :NEW.car_id THEN
      -- doe hier voor elk uniek autoid uit CarDinges.CarIds de dingen die jij wil
    END IF;
    idx := CarDinges.CarIds.NEXT(idx);
  END LOOP;
END;

Even de werking van deze code:
We hebben een type numberlist, we maken daar een variabele CarIds van aan. In deze variabele worden de ids van de auto's in opgeslagen. Misschien dat die variabele ook buiten een package kan worden bijgehouden, dat zou ik zo snel niet weten.

Verder hebben we drie triggers: koppeltabel_gbi (global before insert), koppeltabel_ai (after insert), koppeltabel_gai (global after insert)
De gbi trigger wordt aangeroepen voordat er iets in koppeltabel wordt ge-insert. In deze trigger wordt CarIds opnieuw aangemaakt zodat je een lege lijst hebt.
Dan wordt voor elke insert in de koppeltabel de ai trigger aangeroepen. Deze checkt eerst of het nieuwe car_id al niet voorkomt in de CarIds lijst; komt het id niet voor, dan wordt deze toegevoegd aan de lijst.
Nadat alle ai triggers zijn afgegaan wordt er de gai trigger aangeroepen. In deze trigger loop je door je lijst en doe jij je ding voor elke auto.

Et voila, daar heb je hoop ik bruikbare code. Je zal misschien nu weer met het probleem zitten dat die koppeltabel per item opnieuw met een insert statement wordt gevuld waardoor de gai en gbi triggers te snel getriggerd worden, maar daar vind je wel wat op.

Argh, met deze lange post is Crash_Neo me voor

hey Crash_Neo, waar ken jij Thijs van?

[ Voor 1% gewijzigd door The Fox NL op 19-01-2007 17:54 . Reden: FOR EACH ROW vergeten in de ai trigger ]

[Verwijderd]

Bedankt Crash_neo & The Fox NL.
Morgen eens kijken of dit op deze manier werkend te krijgen is. Wel zit ik nog met het multi-user gebeuren (wat ik was vergeten te vermelden), maar dat probeer ik morgen dan wel op te lossen.

The Fox NL schreef op donderdag 18 januari 2007 @ 23:39:
[...]
Et voila, daar heb je hoop ik bruikbare code. Je zal misschien nu weer met het probleem zitten dat die koppeltabel per item opnieuw met een insert statement wordt gevuld waardoor de gai en gbi triggers te snel getriggerd worden, maar daar vind je wel wat op.
[...]

Als dat het geval is zakt het hele project in elkaar of het moet helemaal omgegooid worden, dus maar hopen van niet... Anders maar iets proberen te regelen met de maker van het softwarepakket die de tabellen vult.