Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    4

    Question Unanswered: :new.<variable_name>

    I am trying to write an 'after insert' trigger for a very dynamic database that inserts all the new values put into a table into an audit table. To do this, I have to write :new.col1, :new.col2, :new.col3 etc into the audit table. I have a loop that puts ['col1','col2','col3', etc.] into a variable named column_name, but I don't seem to be able to dynamically generate the variable name. I guess I am looking for something like an eval function in PL/SQL that could do

    stmt := 'new_value := :new.'||column_name;
    eval(stmt);
    insert into audit_table (:new.id,column_name,new_value,SYSDATE);

    But I haven't found it yet, and I can't select column_name from table_name because table 'table_name' is mutating and Oracle won't let you select on a table that is changing. If anyone can help, it would be greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    I don´t know such a command in PL/SQL ... but what do you think about dynamic SQL. Use the package methods DMBS_SQL.

    I hope this helps ?

    Greetings
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Feb 2002
    Posts
    4

    dynamic SQL

    Hey, I can use dynamic SQL for something like the following

    sql_stmt = 'select :col_name from :table_name where id=:id';
    execute immediate into new_value using col_name, table_name, id

    But this is an actual PL/SQL statement that I need to build on the fly like this

    stmt := 'new_value=:new.'||col_name;
    eval(stmt);
    insert into audit_table values(:new.id,new_value,SYSDATE,col_name);

    Any thoughts would be greatly appreciated. Thanks.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    what´s about

    DECLARE
    cVar VARCHAR2(500) := 0;
    BEGIN
    cVar := 'DECLARE ' ||
    ' cThis VARCHAR2(200) := ''' ||
    'BEGIN ' ||
    ' cThis := :new.' || column_name ||
    ' INSERT INTO and so on and so on ' ||
    'END;';

    .
    .
    .
    do the dynamic stuff
    .
    .
    .

    END;

    Is this what you want to do ?

    Greetings
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

Posting Permissions

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