Results 1 to 10 of 10

Thread: Triggers

  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: Triggers

    I have 1 table with over 100 fields. Any time one of these fields changes I need to be able to record the field that changed and the old and new value. What is the best way to do this without having to specify each field name. If I check all in the update columns section is there any way to reference in the trigger the field that caused the trigger to fire? Or can you suggest another way that will allow me to record changes to any field in a generic way?
    many thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    An UPDATE trigger ...
    I would think of using a cursor with dynamic sql in this case checking
    old -vs- new ... something to this effect ...

    cursor get_col is select column_name from user_tables where ...
    open get_col;
    fetch get_col into v_column_name;
    loop
    exit when get_col%notfound;
    if ld.||v_column_name <> :new.||v_column_name
    then
    ...
    ...
    end if;
    end loop;
    close get_col

    something like that ...
    HTH
    Gregg

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I believe triggers can't be narrow down to a column. What I mean with that is that triggers fire in the event of tables, not any column at all -- unless you especify it so ( when <conditions> .. ). In that event, I believe you will have to write triggers as many columns your table has, each one with a different when condition especifying the column on which they are listening. On the other hand, If you could touch the application layer that is doing these changes, you would have a better tracking system right there -- coding what is necessary to track those changes on it.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    A trigger fires on the event ... ie change to a table (update). You can
    further limit the execution of the trigger by using the when ...

    You definitely don't have to write separate triggers for each column or event !!! That's where you can compare ld -vs- :new column values
    ... You can also use ... if updating('column_name') then ...

    In this case, you only want to "audit" changes to the column itself ... I have
    done this on numerous occasions using the same type of logic stated in the
    prior post

    Gregg

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Ahh.. you learn something new everyday actually, thanks gbrabham. Didn't know about the updating( 'column' ) trick actually. Actually, what I was trying to say in my previous post was that he would need to reference the columns explicity ( letting any generic or dynamic solution - as is my belief he wants to - out ).

  6. #6
    Join Date
    Mar 2004
    Posts
    23
    Thanks for your help.

    can dynamic sql be executed in this way from Oracle 9.2? I've applied the following but I'm getting the message bad bind variable 'NEW.'

    kind regards

    column_name all_tab_columns.column_name%type;

    cursor c_check_all_fields is

    select column_name from all_tab_columns where table_name = 'IFASTACCOUNT';

    BEGIN

    open c_check_all_fields;
    fetch c_check_all_fields into column_name;

    while column_name is not null loop

    exit when c_check_all_fields%notfound;

    if updating (column_name) then

    insert into IFASTACCOUNTCHANGESHISTORY
    values (:new.updatedon, :new.env,:new.accountnumber,column_name,
    :new.||column_name,'2');

    end if;

    end loop;

    close c_check_all_fields;
    Last edited by kerrie; 02-24-05 at 07:08.

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You have to specific new and old in your trigger heading ... ie,

    CREATE OR REPLACE TRIGGER schema.triggername
    AFTER UPDATE
    ON schema.mytablename
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    ...
    ...

    then you can reference
    if :new.columnname <> ld.columnname
    then
    ....
    ....
    end if;

    HTH
    Gregg

  8. #8
    Join Date
    Mar 2004
    Posts
    23
    Hi Gregg

    Sorry - I should have included the full sql. It's below. I think I've included everything but I am still getting the bind error. It I try to concatonate :new. and column_name it just doesn't like it.

    CREATE OR REPLACE TRIGGER TA_USER.TRG_IFASTACCOUNTCHANGESHIS
    AFTER UPDATE
    ON TA_USER.IFASTACCOUNT
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    declare

    column_name all_tab_columns.column_name%type;
    data_type all_tab_columns.data_type%type;
    --v_InsertStmt VARCHAR2(200);
    --v_Cursor1 INTEGER;
    --v_ReturnCode INTEGER;


    cursor c_check_all_fields
    is
    select column_name from all_tab_columns where table_name = 'IFASTACCOUNT';

    BEGIN

    open c_check_all_fields;
    fetch c_check_all_fields into column_name;

    while column_name is not null loop

    exit when c_check_all_fields%notfound;

    if updating (column_name) then

    -- v_InsertStmt := 'INSERT INTO IFASTACCOUNTCHANGESHISTORY(FIELDNAME) values (' || column_name || ')';

    -- DBMS_SQL.PARSE(v_Cursor1, v_InsertStmt, DBMS_SQL.V7);

    -- v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor1);

    -- DBMS_SQL.CLOSE_CURSOR(v_Cursor1);
    insert into IFASTACCOUNTCHANGESHISTORY
    values (:new.updatedon, :new.env,:new.accountnumber, :new.||column_name,column_name,'2');

    end if;
    fetch c_check_all_fields into column_name;
    end loop;


    close c_check_all_fields;





    END;

    /

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your problem relys here.. insert into IFASTACCOUNTCHANGESHISTORY
    values (:new.updatedon, :new.env,:new.accountnumber,column_name,
    :new.||column_name,'2');

    Like I said, you can't really access the triggerings columns dynamically, you will have to write each down in the trigger in order to differentiate them. It is one of the trade off this kind of operation has.

    What you could make is a "generic" procedure to create the trigger for you. Here I made one called create_trigger that receive as a parameter a table_name and create the trigger for it. Things that you may know in order to use this approach are:
    - The procedure calls DDL inside, pursuing therefore a commit.
    - Serialization is likely to happen due to the DDL and trigger approach.
    - You must make sure the table exists before using the procedure, otherwise you will get a ORA-24344.
    - Only supports primitive datatypes ( number, varchar, date.. ).

    Ok, first I created a table to save the changes..
    Code:
    SQL@8i> create table changes (
      2     table_name      varchar2(100),
      3     old_value       varchar2(100),
      4     new_value       varchar2(100),
      5     column_name     varchar2(100),
      6     at_time         date
      7  );
    
    Table created.
    Here I will save the changes and the values of each of the columns my trigger will listen onto. Here I will create a test table called T, and my procedure, create_trigger that will create the trigger for T.
    Code:
    SQL@8i> create table t nologging as select 1 a, a.* from all_objects a;
    
    Table created.
    
    SQL@8i> rem NOTE: THIS IS NECESSARY
    SQL@8i> rem OTHERWISE YOU GET ORA-01031
    SQL@8i> grant create trigger to jmartinez;
    
    Grant succeeded.
    
    SQL@8i> create or replace procedure create_trigger( t in varchar2 )
      2  as
      3     column_name     all_tab_columns.column_name%type;
      4     trigger_str     varchar2(4000);
      5  begin
      6     trigger_str := 'create or replace trigger ' || t || '_trigger';
      7     trigger_str := trigger_str || ' before update on ' || t;
      8     trigger_str := trigger_str || ' for each row';
      9     trigger_str := trigger_str || ' begin ';
     10     for i in ( select data_type d, column_name c
     11                  from all_tab_columns
     12                 where table_name = t )
     13     loop
     14             if i.d in ( 'CHAR', 'NUMBER', 'DATE', 'FLOAT', 'VARCHAR2' )
     15             then
     16                     trigger_str := trigger_str || 'if updating( ''' || i.c || ''' ) then ';
     17                     trigger_str := trigger_str || 'insert into changes ( table_name, old_value, new_value, column_name, at_time ) ';
     18                     trigger_str := trigger_str || 'values ( ''' || t || ''', :old.' || i.c || ', :new.' || i.c || ', ''' || i.c || ''', sysdate ); ';
     19                     trigger_str := trigger_str || 'end if; ';
     20             end if;
     21     end loop;
     22     trigger_str := trigger_str || ' end;';
     23     execute immediate trigger_str;
     24  end;
     25  /
    
    Procedure created.
    
    SQL@8i> exec create_trigger( 'T' );
    
    PL/SQL procedure successfully completed.
    
    SQL@8i> update t set a = 1 where rownum = 1;
    
    1 row updated.
    
    SQL@8i> select * from changes;
    
    TABLE_NAME      OLD_VALUE  NEW_VALUE  COLUMN_NAME     AT_TIME
    --------------- ---------- ---------- --------------- ---------
    T               2          1          A               24-FEB-05
    
    SQL@8i> rollback;
    
    Rollback complete.
    
    SQL@8i> select * from changes;
    
    no rows selected
    
    SQL@8i> update t set created = sysdate where rownum = 1;
    
    1 row updated.
    
    SQL@8i> select * from changes;
    
    TABLE_NAME      OLD_VALUE  NEW_VALUE  COLUMN_NAME     AT_TIME
    --------------- ---------- ---------- --------------- ---------
    T               01-DEC-04  24-FEB-05  CREATED         24-FEB-05
    
    SQL@8i> commit;
    
    Commit complete.
    
    SQL@8i> rollback;
    
    Rollback complete.
    
    SQL@8i> select * from changes;
    
    TABLE_NAME      OLD_VALUE  NEW_VALUE  COLUMN_NAME     AT_TIME
    --------------- ---------- ---------- --------------- ---------
    T               01-DEC-04  24-FEB-05  CREATED         24-FEB-05

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    ALL CAN BE DONE IN A SINGLE TRIGGER : ... EX: USER SQL TO BUILD THE SQL FOR YOU ...

    sql statement to build the calls to a procedure ... check_value

    sql> select ' check_value( '||''''||column_name||''''||
    ', ' || ':new.' || column_name || ', ld.' ||
    column_name || ');'
    from user_tab_columns where table_name = upper('PART')
    ;

    ---- procedure ----



    create or replace
    procedure check_value( cname in varchar2,
    value_new in varchar2, value_old in varchar2 )
    is
    begin
    if ( value_new <> value_old or
    (value_new is null and value_old is not NULL) or
    (value_new is not null and value_old is NULL) )
    then
    insert into messages values
    (cname, value_old, value_new );
    end if;
    end;
    /

    ---- trigger (replace the proc calls with the results from above ---

    CREATE OR REPLACE TRIGGER tbi_partwhs_row
    AFTER UPDATE
    ON PART
    REFERENCING NEW AS NEWDATA OLD AS OLDDATA
    FOR EACH ROW

    BEGIN

    check_value( 'T1' , :newdata.T1, lddata.T1);
    check_value( 'T2' , :newdata.T2, lddata.T2);

    END;
    /

    HTH
    Gregg

Posting Permissions

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