[ORA] update datum na een insert mbv trigger

[FvH]

We hebben een probleem in een applicatie met het inserten van records in de tabel. De applicatie probeert een datum te inserten in format ddmmyyyy terwijl de db dd-mm-yyyy verwacht (met streepjes). De applicatie dient nog een half jaar te lopen voordat er een andere geimplementeerd gaat worden. De leverancier van de huidige applicatie bestaat helaas niet meer.

Aangezien het enige probleem het insert statement is en de datum (heel lelijk) in een varchar veld wordt gedumpt dacht ik het op de volgende manier op te kunnen lossen.

code:

CREATE OR REPLACE TRIGGER update_incorrect_date_format
AFTER INSERT OR UPDATE 
ON WEBDATALOAD.LAAD_OPGAVE
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
     corrected_date    varchar(20);
BEGIN
     SELECT TO_CHAR((TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY')
     INTO corrected_date 
     FROM LAAD_OPGAVE
     WHERE ID = :new.ID; 
     
     UPDATE LAAD_JAAROPGAVE 
     SET EINDDATUM= corrected_date
     WHERE ID = :new.ID;
END;
/

Ik heb met het select statement getest en het lijkt te werken. Wellicht dat ik iets verkeerd doe met de :new zaken. Ik ben niet zo heel erg bekend met triggers. Ik heb me al lam gezocht op het internet, maar gok erop dat ik verkeerde keywords gebruik. Vind iig niet goed wat ik zoek.

Wellicht dat een van jullie me verder kan helpen?

[Nick_S]

Op een pagina over Oracle SQL vond ik het volgende (emphasis mine):

* Only for row-level triggers:
o The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! See example below.
o The REFERENCING clause can be used to assign aliases to the variables NEW and OLD.
o A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger is fired for each row.

[Edit: Sorry, leesfout, er stond WHEN en niet WHERE. Ik zoek nog even verder.]

[Edit2: Het schijnt dat je in je trigger niet de tabel mag aanpassen, die de trigger veroorzaakte:

Mutating Table Errors
Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors:

* A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.)
* A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

[ Voor 31% gewijzigd door Nick_S op 28-11-2007 14:11 ]

[KabouterSuper]

Waarom in godsnaam

code:

     SELECT TO_CHAR((TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY')
     INTO corrected_date 
     FROM LAAD_OPGAVE
     WHERE ID = :new.ID;

in plaats van

code:

     :new.EINDDATUM:=TO_CHAR((TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY');

En wat is het probleem eigenlijk? Compileert de trigger niet? Krijg je een failed-to-update? Of gebeurt er niets?

[FvH]

Krijg een ORA-04084 als ik dat doe. "Cannot change NEW values for this trigger type"

[edit]
En ja, er gebeurd helemaal niets de hele insert van de loader blijft zelf achterwege!
Ik ga eens proberen of ik iets met if (inserting) e.d. kan gaan doen. Misschien dat ik gewoon een volledig insert command moet opnemen in mijn trigger?

[ Voor 60% gewijzigd door FvH op 28-11-2007 14:21 ]

[KabouterSuper]

Oeps....het is een after insert or update trigger. Sorry, ik dacht dat je een before trigger gebruikte.
Dan wordt het:

code:

     corrected_date:=TO_CHAR((TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY');

Maar wat werkt er niet?

[FvH]

Dit laatste (je opmerking i.z. before/after) werkt wel, maar past de value in de tabel niet aan. Nogal logisch lijkt me, want corrected_date staat gewoon als var gedeclared bovenin. Ik zie nu wel dat corrected_date wel de goede values bevat iedere keer, maar het wordt niet in de record gezet. Gewoon update-query erachter plakken?

[Surehand]

Aangezien je gewoon kunt beschikken over de inhoud van de kolommen, zou je de corrected_date beter als volgt kunnen vullen:

code:

corrected_date := TO_CHAR((TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY');

Hierna kun je de update uitvoeren op de andere tabel (althans, dat is wat ik uit je code haal. Als het dezelfde tabel is klopt je update niet en kun je het statement van KabouterSuper gebruiken.)

[KabouterSuper]

Als ik het goed gegrijp, wordt geprobeerd een string '01012007' in het veld EINDDATUM te inserten, maar je wilt dat er '01-01-2007' geinsert wordt.

Dit los je op met een before trigger, waar je simpelweg de :new.EINDDATUM aanpast. Dus:

code:

CREATE OR REPLACE TRIGGER update_incorrect_date_format
BEFORE INSERT OR UPDATE 
ON WEBDATALOAD.LAAD_OPGAVE
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
     :new.EINDDATUM:=TO_DATE(:new.EINDDATUM, 'DDMMYYYY')), 'DD-MM-YYYY');
END;
/

Omdat je de waarde manipuleert voordat je de actie uitvoert, hoef je in de trigger geen update statement meer uit te voeren.

Jouw after statement zal een loop veroorzaken, omdat jouw update in de trigger diezelfde trigger weer aanroept.

[bigbeng]

KabouterSuper schreef op woensdag 28 november 2007 @ 14:28:
...
Jouw after statement zal een loop veroorzaken, omdat jouw update in de trigger diezelfde trigger weer aanroept.

Oracle staat dit niet toe, dus een loop zal er niet ontstaan. Wel knalt ie eruit met een lelijke foutmelding.

[FvH]

Dit werkt en eigenlijk zo'n domme denkfout. Dank!