CODEWITHSUNDEEP
oracle-databaseplsqltriggersdatabase-cursor
Crystal Paladin
Crystal Paladin

Reputation: 589

Efficient way to get updated column names on an after update trigger

I've come up with the following trigger to extract all the column names which are updated when a table row update statement is executed...

but the problem is if there are more columns(atleast 100 cols), the performance/efficiency comes into concern

sample trigger code:

set define off;
create or replace TRIGGER TEST_TRIGG
AFTER UPDATE ON A_AAA
FOR EACH ROW
DECLARE
    mytable varchar2(32) := 'A_AAA';
    mycolumn varchar2(32);
    updatedcols varchar2(3000);

    cursor s1 (mytable varchar2) is 
        select column_name from user_tab_columns where table_name = mytable;
begin

        open s1 (mytable);

        loop
            fetch s1 into mycolumn;
            exit when s1%NOTFOUND;

            IF UPDATING( mycolumn ) THEN
                updatedcols := updatedcols || ',' || mycolumn;
            END IF;

        end loop;
        close s1;
        --do a few things with the list of updated columns
    dbms_output.put_line('updated cols ' || updatedcols);
end;
/

Is there any alternative way to get the list?

Maybe with v$ tables (v$transaction or anything similar)?

Upvotes: 4

Views: 4567

Answers (2)

vadipp
vadipp

Reputation: 948

Faced with a similar task (audit modifications done to table rows), we ended up writing a pl/sql procedure which lists the columns of the table and generates the full trigger body for us, with static code referencing :new.col and :old.col. The execution of such trigger should probably be faster (though we didn't compare).

However, the downside is that when you later add a new column to the table, it's easy to forget to update the trigger body. It probably can be managed somehow with a monitoring job or elsehow, but for now it works for us.

P.S. I became curious what that updating('COL') feature does, and checked it now. I found out that it returns true if the column is present in the update statement, even if the value of the column actually didn't change (:old.col is equal to :new:col). This might generate unneeded history records, if the table is being updated by something like Java Hibernate library, which (by default) always specifies all columns in the update statements it generates. In such a case you might want to actually compare the values from inside the trigger body and insert the history record only in case the new value differs from the old value.

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

No its the best way to get UPDATED column by UPDATING()

and you can change your code using implicit cursor like this, it will be a little bit faster

set define off;
create or replace TRIGGER TEST_TRIGG
AFTER UPDATE ON A_AAA
FOR EACH ROW
DECLARE
updatedcols varchar2(3000);
begin
for r in (select column_name from user_tab_columns where table_name ='A_AAA')
    loop
       IF UPDATING(r.column_name) THEN
          updatedcols := updatedcols || ',' || r.column_name;
       END IF;
    end loop;
    dbms_output.put_line('updated cols ' || updatedcols);
end;
/

Upvotes: 1

Related Questions