Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: Log (dml) to logfile from within trigger

    Hi,

    I'm looking for a way to log the dml statement -or (error) messages- from the triggerbody to a logfile.

    I got an application which inserts records into a table, but is not programmed to catch errors (returned by an 'raise_application_error' in the trigger on the table).

    Of course I can update an 'error-log-table' but I would like to log the whole dml (or my own error message) to logfile as well. (Oracle9i)

    Is that possible an if so: how?
    Anybody?

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Log (dml) to logfile from within trigger

    We actually log the DML to a table not a file. We have a Log Schema that has the same table structure as the production schema accept each table has Update_Time, Update_User, and Update_Type (I = Insert, D = Delete, U=Update) This allows us to keep track of each modification, and query it when necessary. It is done in a Logging trigger. Below is the code to determine the type of DML.

    Code:
        IF INSERTING THEN
          lv_logtype := 'I';
        ELSIF UPDATING THEN
          lv_logtype := 'U';
        ELSE
          lv_logtype := 'D';
        END IF;
    For inserts and updates we use the :NEW keyword to write the new data to the log table. For deletes we use the :OLD values.

    I'm sure you can tweak this mentality to write records to a file instead of a table. And you can build the actual DML statment if that is necessary.

    ie:

    Code:
       lv_dml_statment := 'Insert Into Table_Name(Col1,Col2) ' || 
            'Values (''' || :NEW.Col1 || ''',''' || :NEW.Col2 || ''')';
    HIH

Posting Permissions

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