Home » SQL & PL/SQL » SQL & PL/SQL » get the name of the column being updated by query (oralce 10g)
get the name of the column being updated by query [message #409378] Mon, 22 June 2009 06:26 Go to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi
how can i get the name of the column being updated by query
I need to insert the name of column being modified in an update query to a history table using a trigger created on the main table.
how do I get the column name being updated in this trigger


eg update test set emp_name = 'sapan' where emp_pos='president';

now i want to insert 'emp_name' in the table test_hist as follows

====================================
colmn name old value new value updated on updated by
====================================
emp_test notsapan sapan sysdate syscontext_user
====================================

thanks in advance
Sapan
Re: get the name of the column being updated by query [message #409398 is a reply to message #409378] Mon, 22 June 2009 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) This isn't really a forms question.
2) There is no easy way of doing this in a trigger. The hard way is to check the new and old values of each column in turn and set the column name accordingly.
3) What do you do if the update sets more than one column?
4) Oracle gives you auditing for this purpose.
Re: get the name of the column being updated by query [message #409481 is a reply to message #409398] Mon, 22 June 2009 20:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Try the updating('<COLUMN_NAME>') expression.

create or replace trigger
...
for each row
begin
   if updating('col1') then ... end if;
   if updating('col2') then ... end if;
...
end;
/

update t1 set
        col1 = col1 
      , col2 = col2
/

for the update above, the expression updating('col1') is true as is updating('col2'), however, assuming col3 is also in the tables, updating('col3') is false.

Notice that updating('...') does not care if the values are the same, it only cares that the column was in the update as an updated column.

Does this do what you want?

Kevin
Re: get the name of the column being updated by query [message #410090 is a reply to message #409481] Thu, 25 June 2009 03:05 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi Kevin

Thanks

As for this solution i would have to include an if for every column in the table/s in the trigger.
that is quite some work

i wanted to know if here is some variable holding the column name being updated and use this to insert in the history table.

Sapan

Re: get the name of the column being updated by query [message #410095 is a reply to message #410090] Thu, 25 June 2009 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I really keep wondering why developers seem to be so afraid of doing "quite some work". See it more often here that people spend days uptil weeks to avoid having to code some extra lines. Lines that can often (as in this case) easily be generated.
Re: get the name of the column being updated by query [message #410181 is a reply to message #410095] Thu, 25 June 2009 09:58 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
What variable? Explain to me in more detail what you mean?

update T1 set
        col1 = ...
      , col2 = ...
      , ...
      , col999 = ...
/

This update is updating 999 columns, what kind of variable were you looking for, to hold "THE" column name? What was "THE" column name you were looking for anyway? If you can show us an example of what you want maybe we can give it to you. Otherwise, I think maybe you have not thought through what you are saying.

In any event, as Frank points out, stop whining and start writing code. It is what it is. If you want it, do it.

You should think about SQL FROM SQL. This is one of those ways that Frank alluded to, to generate your code. This is possibly the single most important code writing skill for any Oracle developer/DBA. Here is an example:

select '   if updating(''||column_name||'') then ... end if;'
from user_tab_columns
where table_name = 'AGENCY'
/

yeilds this output:

   if updating('ID') then ... end if;
   if updating('STATE_LICENSE_NAME') then ... end if;
   if updating('TAX_ID') then ... end if;
   if updating('LEGAL_ENTITY_NAME') then ... end if;
   if updating('DBA_NAME') then ... end if;
   if updating('AGENCY_MODEL_TYPE_ID') then ... end if;
   if updating('OWNERSHIP_TYPE_ID') then ... end if;
   if updating('ADDITIONAL_INSTRUCTIONS') then ... end if;
   if updating('CHANGE_DT') then ... end if;
   if updating('TRANSACTION_SEQ_NO') then ... end if;
   if updating('PROSPECT_CODE') then ... end if;
   if updating('PL_REP_PARTY_ID') then ... end if;
   if updating('SC_REP_PARTY_ID') then ... end if;
   if updating('HFP_REP_PARTY_ID') then ... end if;


no work there. Here are some links that might help you with this:

Kevin Meade's ORAFaq Blog

The Phoenix Rises: a How-To for SQL FROM SQL

Good luck, Kevin
Previous Topic: sql statement executing for a very long > 1 hour
Next Topic: Display Oracle Data in Hierarchical format
Goto Forum:
  


Current Time: Sun Dec 04 00:23:39 CST 2016

Total time taken to generate the page: 0.10920 seconds