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

    Unanswered: save all old values in one time

    Hello,

    Can anybody tell me how i can save all columns of a deleted record in an after delete for each row trigger. I have an array of ROWTYPES called oldrows and then I want to do something like this:
    blddelrow_pkg.oldrows( i ) := :OLD;
    Instead of copying column by column. Anyone has an idea how to do it?

    CREATE OR REPLACE TRIGGER ish.BldDeleteSaveValues
    AFTER DELETE ON ish.Building
    FOR EACH ROW
    DECLARE
    i NUMBER DEFAULT blddelrow_pkg.oldrows.count + 1;
    BEGIN
    blddelrow_pkg.oldrows( i ).BldComplexBldId := :OLD.BldComplexBldId;
    blddelrow_pkg.oldrows( i ).SpcaId := :OLD.SpcaId;
    etc. etc.
    END;
    /

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

    Re: save all old values in one time

    Originally posted by thepercival
    Hello,

    Can anybody tell me how i can save all columns of a deleted record in an after delete for each row trigger. I have an array of ROWTYPES called oldrows and then I want to do something like this:
    blddelrow_pkg.oldrows( i ) := :OLD;
    Instead of copying column by column. Anyone has an idea how to do it?

    CREATE OR REPLACE TRIGGER ish.BldDeleteSaveValues
    AFTER DELETE ON ish.Building
    FOR EACH ROW
    DECLARE
    i NUMBER DEFAULT blddelrow_pkg.oldrows.count + 1;
    BEGIN
    blddelrow_pkg.oldrows( i ).BldComplexBldId := :OLD.BldComplexBldId;
    blddelrow_pkg.oldrows( i ).SpcaId := :OLD.SpcaId;
    etc. etc.
    END;
    /
    No, unfortunately it simply isn't possible - you are not the first person to ask this question!

    If you need to write a lot of such triggers, you could write a procedure based on USER_TAB_COLUMNS to generate the trigger code like this:

    Code:
    FOR r IN (SELECT column_name FROM user_tab_columns
                   WHERE table_name = p_table_name )
    LOOP
      DBMS_OUTPUT.PUT_LINE( p_pkg_name || '.oldrows(i).' || r.column_name || ' := :OLD.' || r.column_name || ';' );
    END LOOP;

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

    thanks

    thank you

Posting Permissions

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