Auditing transaction tables

Hello List,

One of my clients wants to audit some transactions tables, some of which get a high volume of transactions throughout the day. Here are the tables they want to audit.

F4101
F4102
F3002
F3003
F4801
F3112
F3111

My question is has anyone done this to these tables or tables like this which have higher transaction volumes? What was the approach and how did it affect performance? My guess is that database triggers is the way to go but should they be custom triggers or is the CFR functionality in JDE just as useful since it's building and using database triggers to do the audit in the background anyway?

Any thoughts or concerns are appreciated.

Thank you

Junior,

FYI these are NOT Transaction tables. They are a mix of master tables and master child tables.

F4101 - Item Master
F4102 - Item Branch Master
F3002 - Standard Bill of Material
F3003 - Standard Routings
F4801 - Work Order Master
F3111 - Work Order Parts List
F3112 - Work Order Routing

If by "Auditing" you mean recording every Add, Change, Delete to each of these tables them yes, we do that.

We have a database triggers set up to record changes into separate audit tables for each of the above (and other tables). We looked at the CFR method JDE provides and found it to be over complicated.

We just created F55xxxxL tables (such as F554101L) that the data base triggers write after images into. Then we created very simple JDE apps that can query these tables and linked the appropriate standard JDE apps to the Audit apps. So for example in Item Master Maintenance I can row exit on a Item to see the history of changes to that item.

Performance has not been an issue. However space can be. Certain UBE's that may run frequently and update a table you may want to filter out. Example: Lead Time Rollup that runs every night here updates every F4102 record whether there's a change or not, so we have the database trigger filter out changes such as those.

Here's a example trigger on changes to the F4102 table:

CREATE OR REPLACE TRIGGER F4102_WAGAUD_T2 BEFORE
UPDATE ON "F4102" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
WHEN ( new.IBITM > 0 AND NOT TRIM(new.IBPID) IN ('P30201','R3482','R30822A','ER43990','R42995','EP3111','ER31410','P5541005','EP41413','EP4112','EP31113','EP4113','EP4312','EP4114','EP4205','EP4210','EP3002','EP48013','EP31225','EP3411') )
BEGIN
INSERT INTO F554102L VALUES
('CHG',
:new.IBLITM,:new.IBSRP1,:new.IBSRP2,:new.IBSRP3,
:new.IBSRP4,:new.IBSRP5,:new.IBSRP6,:new.IBSRP7,:new.IBSRP8,
:new.IBSRP9,:new.IBSRP0,:new.IBPRP1,:new.IBPRP2,:new.IBPRP3,
:new.IBPRP4,:new.IBPRP5,:new.IBPRP6,:new.IBPRP7,:new.IBPRP8,
:new.IBPRP9,:new.IBPRP0,:new.IBVEND,:new.IBANPL,:new.IBBUYR,
:new.IBGLPT,:new.IBROPI,:new.IBROQI,:new.IBRQMX,:new.IBRQMN,
:new.IBWOMO,:new.IBSAFE,:new.IBCKAV,:new.IBIFLA,:new.IBSTKT,
:new.IBLNTY,:new.IBCYCL,:new.IBINMG,:new.IBTX,:new.IBTAX1,
:new.IBMPST,:new.IBMERL,:new.IBLTLV,:new.IBLTMF,:new.IBLTCM,
:new.IBOPC,:new.IBOPV,:new.IBACQ,:new.IBMLQ,:new.IBLTPU,
:new.IBMPSP,:new.IBMRPP,:new.IBITC,:new.IBMTF1,:new.IBMTF2,
:new.IBMTF3,:new.IBTIMB,:new.IBMULT,:new.IBUSER,:new.IBPID,
:new.IBJOBN,:new.IBUPMJ,:new.IBTDAY,
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'),:new.IBMCU,
:new.IBITM,:new.IBORIG,:new.IBSRCE,:new.IBBACK,:new.IBSHCM,
:new.IBCARS,:new.IBCARP,:new.IBSHCN,:new.IBSRNR,:new.IBECO,
:new.IBECTY,:new.IBECOD,:new.IBMOVD,:new.IBQUED,:new.IBSETL,
:new.IBURCD,:new.IBURDT,:new.IBURAT,:new.IBURAB,:new.IBURRF,
:new.IBPEFD,:new.IBSCPSELL);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

I'd guess we'd need to know a little more about "what's" expected to be audited?

Not knowing, I would assume you would target database triggers (not Table Event Rules) that populate your audits for Add, Edit/Update & Delete?

(db)

I have used CFR at 2 locations in the past without too much overhead. We then built reports over them so that Internal Audit can audit the changes to the tables. The one drawback I found is that CFR does not record updates that are triggered from non JDE sources. So if you use SQL external to update the table CFR will record it as ThirdParty. It takes a little knowledge and effort to setup but once it is up it is up. You just have to be careful to not over audit.

Hi Junior,

I've seen both approaches used at different sites and they've both proved reliable and seem to add little overhead to general interactive application processing even when applied to transactional tables (F03B11, F0411 etc).

CFR (I've used with E8.10 on DB2 and it covered all the tables you listed) comes shipped with JDE so is more of a configuration exercise. It's supported by Oracle and they supply some tools to query and report on the data within JDE. The downside we found is it can complicate data refreshes from your CFR environment. As you've noted it does create actual DB triggers so it will audit all operations if required, even if done outside JDE e.g. in SQL.

More commonly I've seen database triggers developed from scratch and this would be my preferance, though will take longer to implement and test but gives you greater control and avoids the complications you get with tables being altered in CFR.

Cheers

Neil.

Thanks for your thoughts everyone. Just to clarify we would want to audit all the inserts, updates and deletes. The reason I throught about going with the CFR functionality over building our own triggers is because we would want to know who did the delete when one is ever done. Just taking the before image before a delete won't give us what we want and the CFR functionality will be able to give us the who of the delete. If I'm wrong in my thinking please let me know.

Thanks again to all.

We have a product that does all this for you if you use an AS/400

I have heard that CFR 21 is not easy to use as it means recreating tables.

I have used triggers quite a bit but some versions of JDE they dont work quite as you would expect so you need to test.