| Trigger on standard apps table. [message #160125] |
Thu, 23 February 2006 07:02  |
sambireddyn
Messages: 48
Registered: May 2005
Location: Andhra Pradesh
|
Member |
|
|
Hi
I want to write a trigger on standard apps table which will update last_update_date column when new record is inserting or any updates on that table.
How to write this trigger?
If I create any trigger on apps table will the performance reduces?
Will this impact any thing like forms data retrivial or other tables?
Thanks in advance.
Regards,
SambiReddy.
|
|
|
|
| Re: Trigger on standard apps table. [message #160137 is a reply to message #160125] |
Thu, 23 February 2006 08:17  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
When you update APPS tables through Oracle Apps, last_update_date is already automatically managed for you.
You're not directly updating an APPS table, are you?| Quote from the Oracle Applications Developers Guide |
Do Not Use Database Tools to Modify Oracle Applications Data
We STRONGLY RECOMMEND that you never use SQL*Plus, Oracle Data Browser, database triggers, or any other tool to modify Oracle Applications tables, unless we tell you to do so in our guides.
Oracle provides powerful tools you can use to create, store, change, retrieve, and maintain information in an Oracle database. But if you use Oracle tools such as SQL*Plus to modify Oracle Applications data, you risk destroying the integrity of your data and you lose the ability to audit changes to your data.
Because Oracle Applications tables are interrelated, any change you make using an Oracle Applications form can update many tables at once. But when you modify Oracle Applications data using anything other than Oracle Applications forms, you might change a row in one table without making corresponding changes in related tables. If your tables get out of synchronization with each other, you risk retrieving erroneous information and you risk unpredictable results throughout Oracle Applications.
When you use Oracle Applications forms to modify your data, Oracle Applications automatically checks that your changes are valid. Oracle Applications also keeps track of who changes information. But, if you enter information into database tables using database tools, you may store invalid information. You also lose the ability to track who has changed your information because SQL*Plus and other database tools do not keep a record of changes.
|
|
|
|
|
| Re: Trigger on standard apps table. [message #160145 is a reply to message #160125] |
Thu, 23 February 2006 08:59 |
sambireddyn
Messages: 48
Registered: May 2005
Location: Andhra Pradesh
|
Member |
|
|
Hi,
We have different scenario, Our MIS is customised module.
MIS database will update their data periodically based on
last_update_date of oracle apps tables (some ).
In some cases we do datafixes ( back end updates ) on standard apps tables. In that case last_update_date will not be updated.
MIS procedure look for Last_update_date and they will update their data base. But in this case MIS data is not updating.
So the requirement is to write a trigger on apps table to update last_update_date column, if any datfixes happens. so that MIS data will also be updated.
I want to create a trigger on apps table ( on last_update_date to sysdate ) when ever any update happens.
Could you please help.
Regards,
SambiReddy.
|
|
|
|
| Re: Trigger on standard apps table. [message #160157 is a reply to message #160145] |
Thu, 23 February 2006 10:21 |
adragnes
Messages: 241
Registered: February 2005
Location: Oslo, Norway
|
Senior Member |
|
|
Instead of creating a trigger on the table in question consider making sure that the inserts and updates you do in conjunction with manual datafixes also update the LAST_UPDATE_DATE and LAST_UPDATED_BY columns by manual procedures. I cannot imagine that this is such a frequent occurence.
If this proves to be inefficient you should consider other options. There certainly are ways to do this without creating database triggers on standard tables. You could for instance create a view on the table with instead of triggers that make sure that the the WHO-columns are updated. An alternative would be to create an API to use for updating the table to the same effect.
You could also possibly replace the custom table with a materialised view.
--
Aleksander Dragnes
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
