J.D. Edwards or DB2/400 trigger

Hello List.

We have a requirement to add a trigger to one of our base J.D. Edwards payroll tables. We are having some internal discussions around using a J.D. Edwards trigger versus a DB2/400 trigger. What I was hoping you could share is your experience with J.D. Edwards triggers and if you’ve had any issue(s) with them not functioning correctly as designed? I’d also like to know if you remove the triggers prior to running the TC workbench during an application release upgrade, maybe even an ASU if the table is going to have its data copied out and back in? Any other pros or cons would be appreciated.

Thanks in advance,

Michael
9.0 / 8.98.4.7 / IBM i V6R1 / OAS

Hi Mike,

JDE or DB2/400 triggers? It depends...

If your trigger just needs to run a bunch of DB2/400 instructions (like writing to an audit table or validating against some other DB2/400 table),
then go with native triggers. According to my experience, they provide better performance than JDE ones.

On the other hand, native triggers are not portable to other platforms (if your company decides to switch to another database technology, these
triggers will have to be rewritten from scratch).

Regarding your ASU question, I'd definitely disable them before launching any ESU/ASU/release upgrades.

Finally, when would JDE triggers be useful? In the case you need to run BSFNs within the trigger code.
BSFNs can't always be replaced by a bunch of SQL statements, so JDE triggers may come handy.


Regards

To add to Sebastien's excellent post ...

WARNING: JDE Table Triggers are NOT the same as DB Table triggers - don't expect them to behave the same. Everyone should experience their quirks though so go ahead - it might work for what you want.

Telling us what you're trying to accomplish (you didn't say) might get more specific advice.

Michael,

This is a "heads up" and you will need to do your own research.

Another thing to keep in mind with JDE triggers is that the table columns may only be populated with data from the access (select, update, insert, delete) to the table, so it may be possible, even likely, that not all the columns will be populated. This may also vary (in some circumstances all columns may be populated) as to where the access originates (web, fat client, server).

This was my experience some time ago (E8.11/TR8.9.7) and things may have changed since.

Generally speaking, IMO, table triggers (any type) should really only be used as a last resort. I much prefer to look for all other possible solutions and only resort to triggers if there really is no other way. This is again, generally speaking. Sometimes triggers are the best solution. Sort of like limb amputation in the medical profession.

Having said that, IMO JDE Table Triggers are borderline useless. Keeping with the medical analogy its like amputating your head.

Thank you for all of your responses, much appreciated.

This is what we’re trying to accomplish. We’re going to create a new custom table based on the F08402. The custom part will be adding additional columns such as business unit, user profile and one or two more fields. We want to add a trigger to the F08042 that will update the custom F5508402 table and the team is planning on using a J.D. Edwards trigger to do so. If I’m not mistaken, the reason for this is so we can get a couple fields in the custom table that our 3rd party reporting tool (which inherits most of the J.D. Edwards security) will read instead of the F08402 table.

Not being a developer and/or much experience with triggers other than what they accomplish, I was hoping to confirm the J.D. Edwards trigger is the best way to go and what if anything do I need to be concerned about should the F08402 be ‘dropped’ and re-created during a TC workbench thinking that if the data is copied out pre the TC and back in post the TC what impact this will have on our custom F5508402?

Thanks again.

Michael

Hi,

If you just have to pickup a few fields to populate
F5508402 from F08402 and other tables, then go with
a native DB2/400 trigger.

However, if there's more logic behind (like invoking
BSFNs) then go with a JDE trigger.

Be aware that JDE triggers are BSFNs, so they
are "visible" only within the ERP itself.

For example, a JDE trigger won't launch if you
insert a record on F08402 via STRSQL or MS-Access
or flat-file import, so, only SQL operations
originated from JDE will be catched by this trigger.

On the other hand, DB2/400 trigger will fire no
matter where the table operation comes from.

By the way, I never tested what happens when you
drop a table that has a JDE trigger linked.

In both cases (whether native or JDE triggers), I'd
disable them before running any ASU affecting
that particular table.

Hi Sebastian. Thank you for your reply. As an FYI, the team decided to move forward with the J.D. Edwards trigger. Seems to working well as designed. Thanks again, Michael