Table Auditing (Discussion)

[DBohner-(db)]

Me, Again...

So, client wants to audit a column within a table for changes (for example: F0411.PYE). There are so many ways that the change can be audited - but trying to convince the client (and myself) is becoming fruitless.

One concern is Disk Limitations(sizing), another was performance and, and, and ....

Most common ways I am aware of are:

CFR 21 Part 11:
+ Built into EnterpriseOne
+ Standards Based (Not a DBA Customize-able)
+ Life Sciences "Standard"
+ Audits for Third Party
- Requires a System Bounce to turn on/off
- Additional DBA Efforts during Refreshes to other Environments
- Uses _ADT Tables and SQL Views

Database Triggers:
+ Audits Third Part
+ Can be Very Customized
- Additional DBA Efforts during Refreshes to other Environments
- Not as simple to turn on/off

Table Event Rules:
+ Does not require Additional DBA Efforts
+ Can be Very Customized
+ Does not require System Bounce
- Limited to E1 Only (does not track third party)

There's a ton more pros/cons to each, and there might be some other ways to track changes that I'm missing.

When the goal is to track changes to one or more columns in a table - what is the favored approach & Why (and don't suggest that the users write each change down on paper)....

(db)

 

[craig_welton]

While it may seem cumbersome the CFR solution is able to audit both E1 and third party (non JDB) completely. If you just use a native DB trigger, you will not have the JDE user ID who made the change (yes the stock audit fields MAY have the proper value, but what about a delete?)

You can employ a combination of both JDE Table triggers and DB triggers, but that's a little ugly.

just my 2p
Craig

 

[Larry_Jones]

Since the CFR solution is basically DB triggers doesn't it have the same issue regarding UserID as a custom DB trigger? It's still dependant on the application code to fill in the audit fields right?

JDE Table triggers are just unreliable / weird in my opinion. They seem to have been built for a different purpose.

Advantages of a custom DB trigger include:
- You decide what to audit and what not to
- Can exclude repetitive updates from certain batch jobs
- Ez to build custom JDE Inquiry forms to view Audit table history (and connect into related application forms via row exit).
- Doesn't rename base tables - which is easier on external code and reporting solutions.
- Its basically a simpler approach that works.

 

[craig_welton]

Larry, I think the CFR implementation actually does figure out the JDE user. The audited table (F0101_ADT for example) has a set of CFR audit columns and those get loaded with the current user's info at the time of the action. This is done by the JDB middleware when auditing is enabled. These actions are stored in the F9500004 as well with the GUID you see in the ADT table. Those CFR audit columns are what gets written to the A0101 table by the DB trigger.

What's cool is during a delete, the middleware actually UPDATES the F0101_ADT record but sets a Delete flag to Y. So the CFR columns are populated and the A0101 gets the audit info of the person who did the delete. The DB update trigger then actually performs the delete.

In the case of a non JDE update, the CFR columns are not populated, so the trigger uses the current database user and machine name to populate the audit columns in A0101.

 

[Jaise James]

Hi there

I like jde audit as it gets you e1 user I'd properly thAt you may not get correctly with db audit

One thing I will warn is about the table it self. This is transaction table and I hate to turn auditing on any transaction table for its obvious performance concerns

I remember I had Almost the same requirement in my organization and I was able to dissuade them an they were actually able to change a process a bit to avoid the need altogether

One thing I did do during the testing was that we had replication turn on for all the tables. So I decided to turn simple audit on the far end db for this table instead in the live transaction db it self

 

[Larry_Jones]

Thanks for the Explanation Craig

 

[Abir Mannan]

Hey Dan,
i was working on a similar thing in my present client where needed to audit PRP0 in F4211.
Since they do not have CFR turned on and also that will not help since it has its own standard functionality to maintain audit.

I had to create DB trigger instead of JDE table trigger since JDE table trigger is lacking to get the Before Update value. I am not sure why they even use that even in the trigger.
in order to get the Before Update value we had to use DB trigger.
Thanks
Abir Mannan

 

[DBohner-(db)]

Hi Abir,

Anyway, back to the issue. In the 'Before Update' event, if you are not worried about performance, you can do a fetch. Since the Primary Keys don't change - you can use the keys to yank the values during the 'before'.... NOT A PRETTY SOLUTION.

Agree - not sure why bOrgacle has a Before Update, unless they expect us to fetch?

There is an INI switch that is to populate the before values. But, through reading - I could never determine if it only populates the after values. It appeared that if the setting did not exist, the TK values were empty in the Before event... Since we do have the CFR Option here - we are going that direction.

BTW -
It's Been a few months, hope things are GREAT!

(db)

 

[mhkatakam]

Does table auditing have any performance issues? I would appreciate if any one can shed light on this topic.