| Triggers for oracle apps tables [message #485190] |
Mon, 06 December 2010 12:20  |
 |
gsrikanthsharma
Messages: 6
Registered: December 2010
|
Junior Member |
|
|
Hi friends,
I have a cumbersome question for you.
When two users update two front end forms in oracle apps,the tables of that particular forms get updated.
Now on this I would like to have audit trial,i.e., I will have an audit table in which i will store the information
of column name of a particular table,its old_value,new_value, module_name,updated_by,etc...
Suppose that two users from two different front end forms has updated same table may be same column.
For audit trial i will write two triggers for these two forms,i.e.. i will write two triggers with if updating clause with
columns which are going to be affected by those forms in the back end.
My problem is...
In both triggers some columns may match. If any user updates any of the matching columns from one form both the triggers will fire because both the triggers are having same column(if updating(same column)) and i will not be able to know which user has updated that column.
How can i write these triggers differently for two different forms?
|
|
|
|
| Re: Triggers for oracle apps tables [message #485192 is a reply to message #485190] |
Mon, 06 December 2010 12:34  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Are you sure this is legal? Creating a trigger sounds like something that would require Enterprise Edition database licenses, just like creating a stored procedure. Better check that server is licensed before proceeding.
But apart from that, you shouldn't do it: it is not supported in EBS, and will cause problems with upgrades. You should use the Alert system instead, it is a supported (and legal!) way of creating triggers, all documented in the Oracle Alert Users Guide.
|
|
|
|
| Re: Triggers for oracle apps tables [message #485196 is a reply to message #485192] |
Mon, 06 December 2010 12:43 |
|
gsrikanthsharma
Messages: 6
Registered: December 2010
|
Junior Member |
|
|
I am working in a small IT company and there is no question of any illegal issues.
we are working on a product which needs audit control on apps tables as per the requirement of client.
It is the client who selects which fields he want to have an audit trial.
All the fields are not audited. If the client feels any field to be important he checks that column for audit trial from a java
console.
In one form he checks different fields and in the other form he will check different fields of same table. Some times these may overlap.
So we are going for different triggers based on forms and fields.
The product has a delivery time less than 10 days. Please help me in this regard...
[Updated on: Mon, 06 December 2010 12:45] Report message to a moderator |
|
|
|
| Re: Triggers for oracle apps tables [message #485200 is a reply to message #485196] |
Mon, 06 December 2010 13:29 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've already suggested not to use triggers, but to use Alerts. But if you want to use triggers, you do need to confirm the licensing: your client will not be pleased with you if you choose an implementation method that will cost him forty seven thousand US dollars per CPU next time he gets audited by Oracle. Surely worth five minutes to check?
However, you need one trigger, not two. The trigger would have to detect the EBS user who has made the update (presumably that is what you want? The Oracle user will always be APPS.) I don't see why the form through which the change was made is relevant.
I'm not going to offer more advice, because you are trying to do this the wrong way. Perhaps someone else can help.
|
|
|
|
| Re: Triggers for oracle apps tables [message #486565 is a reply to message #485196] |
Thu, 16 December 2010 14:55 |
 |
Alien
Messages: 292
Registered: June 1999
|
Senior Member |
|
|
Hi,
So why don't you use the standard eBS auditing functionality? Audit all information, and then report the audit trail based on the audited information?
As for the original question. Only the SCN will guarantee which update took place first.
Regards,
Arian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
