Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    10

    Question Unanswered: Referencing :new and :old in Trigger

    Hello,

    Inside a trigger, is there anyway that we can assign either the :new or the ld table to a declared variable without using #temptable? Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by transpac80
    Hello,

    Inside a trigger, is there anyway that we can assign either the :new or the ld table to a declared variable without using #temptable? Thanks.
    What is "#temptable"?
    This is Oracle Forum and unless someone in your organization created a table in Oracle with that name, it seems you are confused with the SQL Server temporary tables.
    Inside a trigger in Oracle you can assign the new and old columns (not tables) to variables as you choose.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2005
    Posts
    10
    Let me try to make myself clearer.

    I have a trigger to be fired AFTER INSERT OR UPDATE OR DELETE. Inside this trigger, I don't care whether it is UPDATING or INSERTING or DELETING, I want to execute a SQL statement pulling data off either from the ':new' table if it was inserted/updated or from the 'ld' table if was deleted. I don't want to write the same SQL twice like this:

    If Inserting or Updating then
    InsertInto Audit....VALUES :new.City, :new.ZipCode...
    Else
    InsertInto Audit....VALUES ld.City, ld.ZipCode...
    End If

    If everybody says I can't then...I guess I can't. Thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Your confusion lies in stating new and old as tables when new and old refer to columns in the table referenced by the trigger.
    If what you want is to keep an audit trail of the changes to this table, the common practice is to keep the 'old' values when updating/deleting and the 'new' values when inserting:

    Code:
    IF INSERTING THEN
       INSERT INTO AUDIT_MYTAB VALUES (:new.col1, :new.col2,...);
    ELSE
       INSERT INTO AUDIT_MYTAB VALUES (:old.col1, :old.col2,...);
    END IF;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    I wrote a script to do this, but I haven't had the chance to test it yet...run this:

    Code:
    set serveroutput on
    declare
      lv_table varchar2(100);
      lv_tablespace varchar2(100) := '&history_tablespace';
      cursor c is select table_name, column_name from user_tab_columns where table_name = '&table_to_back_up';
      lv_column_list varchar2(32000) := '';
      lv_count number := 1;
    begin
      for i in c loop
        if lv_count > 1 then
          lv_column_list := lv_column_list || ', :old.' || i.column_name;
        else
          lv_column_list := ':old.'||i.column_name;
        end if;
        lv_table := i.table_name;
        lv_count := lv_count + 1;
      end loop;
      begin
        execute immediate 'create table '||lv_table||'_hist tablespace '||lv_tablespace||' as select * from '||lv_table||' where rownum < 1';
      exception when others then
        dbms_output.put_line('Table creation failed.  Perhaps you must drop '||lv_table||'_hist first?');
      end;
      execute immediate 'create or replace trigger makehist_'||lv_table||' before update or delete on '||lv_table||' for each row
                         begin
                           insert into '||lv_table||'_hist values ('||lv_column_list||');
                         end;';
    exception when others then
      dbms_output.put_line('Table '||lv_table||' failed.');
    end;
    /
    Put in the name of the table when asked. I think it should work. It'll create a history table and then a trigger for it.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  6. #6
    Join Date
    Mar 2005
    Posts
    10
    Steve,

    I ran your script and got this error:

    Table creation failed. Perhaps you must drop _hist first?
    Table failed.

    Am I missing something?

    Thanks again.

  7. #7
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Oops...enter a tablespace name when it asks you for history_tablespace, then a table name when it asks for table_to_back_up.

    You must also be logged in as the user who owns the table.

    Like I said, I've never test it, but I think it SHOULD work...
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  8. #8
    Join Date
    Mar 2005
    Posts
    10
    The script created the trigger but it only handles Update & Delete. I also need the Insert on the same trigger but it, of course, does not have the ld table.

    Thanks.

Posting Permissions

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