Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: anyone has the wholy grale?

    Hello everone, me again.

    I have an update trigger and in this trigger the same table is updated. I know this sounds stupid, but are relation model is just like this. no altering possible. because of customers. can anyone give me a hint. i tried packages. but this will delete the package variable when after the first (for example) update if you do more updates at once.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: anyone has the wholy grale?

    Originally posted by thepercival
    Hello everone, me again.

    I have an update trigger and in this trigger the same table is updated. I know this sounds stupid, but are relation model is just like this. no altering possible. because of customers. can anyone give me a hint. i tried packages. but this will delete the package variable when after the first (for example) update if you do more updates at once.
    Is this a recursive scenario? User updates record 1, and trigger updates associated records 1.1 and 1.2, which causes trigger to fire again and update records 1.1.1 and 1.1.2 associated with 1.1, and 1.2.1 associated with 1.2, and so on until the whole "family" has been updated?

    If so, I'm not sure how best to handle it. Would it be possible in the trigger to find ALL the "family" of records affected and update in one go, and then use a package flag to ensure that the trigger does NOT behave recursively?

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    recursive

    Hello,

    It is an hierarchy of spaces. we are working on a spacereservation system. this model is where we have to do it with. I probably found a workaround. For every row i save in a package i also save the depth. in this way i know what i can throw away or not when i'm in my before-statement trigger. i throw everything away above a certain depth. so at the end the array is empty again. but if the transaction is cut off, I have a problem. Because my depth variable is still on (for example 2). So now I have always filled the first two blocks. Here is my code. Any comment is welcome. Syntacticly not correct yet!!
    --------------------------
    CREATE OR REPLACE PACKAGE spcrupdrow_pkg
    AS
    TYPE spcrupdoldrowrecord IS RECORD (
    oldrow ish.SpaceReservation%ROWTYPE,
    depth INTEGER
    );

    TYPE spcrupdnewrowidrecord IS RECORD (
    newrow ROWID,
    depth INTEGER
    );

    TYPE spcrupdoldrowarray IS TABLE OF spcrupdoldrowrecord INDEX BY BINARY_INTEGER;
    TYPE spcrupdnewrowidarray IS TABLE OF spcrupdoldrowidrecord INDEX BY BINARY_INTEGER;

    oldrows spcrupdoldrowarray;
    emptyold spcrupdoldrowrecord;

    newrows spcrupdnewrowidarray;
    emptynew spcrupdnewrowidrecord;

    depth INTEGER DEFAULT 0;
    END;
    /

    CREATE OR REPLACE TRIGGER ish.SpcrIndirectUpdateEmptyArray
    BEFORE UPDATE ON ish.SpaceReservation
    BEGIN
    FOR i IN 1 .. spcrupdrow_pkg.oldrows.count LOOP
    IF ( spcrupdrow_pkg.oldrows( i ).depth > spcrupdrow_pkg.depth ) THEN
    spcrupdrow_pkg.oldrows( i ) := spcrupdrow_pkg.emptyold;
    END IF;
    IF ( spcrupdrow_pkg.newrows( i ).depth > spcrupdrow_pkg.depth ) THEN
    spcrupdrow_pkg.newrows( i ) := spcrupdrow_pkg.emptynew;
    END IF;
    END LOOP;
    spcrupdrow_pkg.depth := spcrupdrow_pkg.depth + 1;
    END;
    /

    CREATE OR REPLACE TRIGGER ish.SpcrIndirectUpdateSaveValues
    AFTER UPDATE ON ish.SpaceReservation
    FOR EACH ROW
    DECLARE
    i NUMBER DEFAULT spcrupdrow_pkg.oldrows.count + 1;
    BEGIN
    spcrupdrow_pkg.oldrows( i ).oldrow.SpcrSpcId := :OLD.SpcrSpcId;
    spcrupdrow_pkg.oldrows( i ).depth := spcrupdrow_pkg.depth;
    spcrupdrow_pkg.newrows( spcrupdrow_pkg.newrows.count + 1 ).newrow := :NEW.ROWID;
    spcrupdrow_pkg.newrows( spcrupdrow_pkg.newrows.count + 1 ).depth := spcrupdrow_pkg.depth;
    END;
    /

    CREATE OR REPLACE TRIGGER ish.SpcrIndirectUpdate
    AFTER UPDATE ON ish.SpaceReservation
    DECLARE
    SpcrNewRow ish.SpaceReservation%ROWTYPE;
    BEGIN
    FOR i IN 1 .. spcrupdrow_pkg.newrows.count LOOP
    IF ( spcrupdrow_pkg.newrows(i).depth = spcrupdrow_pkg.depth ) THEN
    SELECT *
    INTO SpcrNewRow
    FROM ish.SpaceReservation
    WHERE ROWID = spcrupdrow_pkg.newrows( i ).newrow
    ;
    IF ( SpcrNewRow.SpcrDirectId IS NULL ) THEN
    IF ( spcrupdrow_pkg.oldrows( i )oldrow.SpcrSpcId <> SpcrNewRow.SpcrSpcId ) THEN
    blablabla ELSE
    UPDATE ish.SpaceReservation
    SET SpcrContext = SpcrNewRow.SpcrContext
    , SpcrRecStatus = SpcrNewRow.SpcrRecStatus
    , SpcrExported = SpcrNewRow.SpcrExported
    , SpcrEmpId = SpcrNewRow.SpcrEmpId
    , SpcrJobId = SpcrNewRow.SpcrJobId
    , SpcrStartDate = SpcrNewRow.SpcrStartDate
    , SpcrEndDate = SpcrNewRow.SpcrEndDate
    , SpcrStartPrepareDate = SpcrNewRow.SpcrStartPrepareDate
    , SpcrEndCleaningDate = SpcrNewRow.SpcrEndCleaningDate
    WHERE ( SpcrDirectResId = SpcrNewRow.SpcrResId
    AND SpcrDirectReslLineId = SpcrNewRow.SpcrReslLineId
    AND SpcrDirectId = SpcrNewRow.SpcrId )
    ;
    spcrupdrow_pkg.depth := spcrupdrow_pkg.depth - 1;
    END IF;
    END IF;
    END IF;
    END LOOP;
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •