E9.1 Table Trigger On SQL Insert

[FrankCLT]

Hi All,

Is it possible to have a table trigger that will increment a UKID when a SQL does the Insert?

Thanks,
FrankCLT

 

[DaveWagoner]

Hehehe. Everything is possible What's your DB?

I dabbled in triggers on the JDE side and found those to only work with db ops from JDE, so you're talking a true database-level trigger I'm guessing

An alternative from my past: If your UKID is a Next Number, there are hacks I did for mass data updates to get a "good" next number on each record I was inserting. But it was a hack. Those depended on me knowing the # of recs I was inserting, incrementing the actual NN in JDE by at least that # (depending on how active the NN is, might want to increment by n+100, etc), then using my "assigned" nn range with the mass data update. It worked solidly but was rather manual.

Let me know if you're interested in my hack. I can clean up some saved sql to show the ropes.

 

[jolly]

Yes it works. Whether it's a good idea depends.

Is a 3rd party application doing the insert or a jde job?

 

[FrankCLT]

An associate of mine is reading a JSON file and inserting into a JD table via Oracle SQL.

 

[FrankCLT]

It is an Oracle DB, couldn't the DB have the trigger on an insert?

 

[DaveWagoner]

Yes, the issue becomes if the UKID is a JDE-controlled next number or not. You don't want to unsafely step on the JDE next number system when gathering a NN for your direct insert.

Hopefully someone here has experience with oracle sql (not me, so I'll shut up after this post) and maybe has figured out how to call the nn increment busfun from sql?

 

[DSauve]

You should be able to define a SEQUENCE in Oracle, and then use that as the source for a next number in your database trigger. Note that this is completely outside of any JDE next-number, but it will get you unique ID's, which is what you're really after.

 

[FrankCLT]

Maybe someone explained this way and I just didn't understand. For those more DB knowledgeable than me, couldn't you do something like...."Select (count(indoco)+1) from testdta.f57lvin t" and use this output as your next number at time of insert.

You would count the records add 1 and use that output for the next number.

 

[alfredorz]

You could create a database trigger event before insert row and get UKID for your object name of F00022, and assign to column. Later increment UKID and update F00022, for example.
If allways insert from sql, create a database sequence and get value (it increment automatically, for example in oracle is sequence_example.nextval).
As you say, max(indoco)+1, may be, but if you are sure allways insert by the same process and you can controller it.

 

[Neal Scarr]

You need to be careful that you don't get duplicate next numbers if more than one process generates a next number at exactly the same time, but in your case this may not be relevant. The Next Number business function locks the next number record to prevent duplicates.

 

[jolly]

An associate of mine is reading a JSON file and inserting into a JD table via Oracle SQL.

I always try to avoid direct database access from 3rd parties (unless it is a Z file). It's messy for security - both network and database. If you have Orchestrator available that might be a better way? The orchestration can then obtain the next UKID using JDE's business function. That function uses atomic select-for-update so avoids duplicate key risk.

 

[jolly]

Yes, the issue becomes if the UKID is a JDE-controlled next number or not. You don't want to unsafely step on the JDE next number system when gathering a NN for your direct insert.

Hopefully someone here has experience with oracle sql (not me, so I'll shut up after this post) and maybe has figured out how to call the nn increment busfun from sql?

With Oracle REST services you can call APIs from PL/SQL so a stored function could call an orchestration to call the JDE next number BSFN and return it. Unfortunately no customer has asked me to try this yet. It would be quite slow so would not want to include it in a query that processes a lot of rows.

 

[BOster]

If you need a JDE next number then you will need to make a call into JDE from the external process to retrieve the next number. Orchestrator would be the contemporary favorite to do that. One thing you should NOT do is try and update the JDE NN table outside of the JDE APIs. That would run a very high risk of screwing something up.

If the number doesn't need to be a JDE NN then you have a lot more options and it just depends on the nature of the process.

If the records will always be inserted into this table and this is some type of single instance linear batch process (i.e. NOT some type of multi-user application) and the records will always be retained in this table the most straight forward method would be for the third party process to run a select max(UKID) at the beginning to get the last number used and then simply increment it the value with every record insert.

On the other end of the spectrum would be (if the table is a custom table) modifying the table at the DB level for the UKID to be an identity seed. I have done the latter and it comes with several restrictions, you can NEVER gen this table again from within JDE as an example, any table mods would need to be done at the SQL level with alter table sql. There is a variation on the latter, you could create a custom table with only UKID as the only field, mod it to be an identity seed and then the third party process could use it to get a unique number to be used with the target table insert. Another option if this is a custom table would be to use a GUID field instead of UKID if you don't care about PK sequence. Or you could use a compound key, GUID and then UKID that is unique for a given GUID, etc.