get the name of the column being updated by query [message #409378] |
Mon, 22 June 2009 06:26  |
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   |
cookiemonster
Messages: 13965 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 #410181 is a reply to message #410095] |
Thu, 25 June 2009 09:58  |
 |
Kevin Meade
Messages: 2103 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
|
|
|