Table Event Rules (TER) Again

  • Thread starter David Robertson
  • Start date
David Robertson

David Robertson

Reputable Poster
I've browsed through all of the previous posts on TER's, and thanks to everyone for the info that provided.

I have a slightly vexing question though, as far as actually coding the TER goes.

In the TER, specifically on the "before insert", "before update", and "before delete" events, under certain conditions, I need to block the insert/update/delete.

I have tried using the SET TER ERROR system function, but this seems to have no effect what-so-ever. Anyone know how this is supposed to work?

I tried changing the key values to NULL, it actually works perfectly, and the user does not see any error message (although this may not be the case from all APPL's), but the JDE.LOG shows:
OCI0000178 - Unable to execute - 0 - INSERT INTO TESTCTL.F0005 (DRSY, DRRT, DRKY, DRDL01, DRDL02, DRSPHD, DRUDCO, DRHRDC, DRUSER, DRPID, DRUPMJ, DRJOBN, DRUPMT) VALUES :)BND1,:BND2,:BND3,:BND4,:BND5,:BND6,:BND7,:BND8,:BND9,:BND10,:BND11,:BND12,:BND13)
138/41 Mon Nov 05 16:18:09 2001 DBPERFRQ.C384
OCI0000179 - Error - ORA-00001: unique constraint (TESTCTL.F0005_1) violated
138/41 Mon Nov 05 16:18:09 2001 jdb_drvm.c887
JDB9900401 - Failed to execute db request
138/41 Mon Nov 05 16:18:09 2001 jdb_exet.c4432
JDB3400009 - Failed to perform Insert for F0005
I would like to leave the logs a bit cleaner than this.
Any ideas greatly appreciated.

Dave
OW B733.2 sp16... Oracle on RS/6000
 
Hi Dave,

You can disable TER triggers, but the only way I know how is to do it in a C function.

You can disable any trigger type (insert,update,delete,fetch) at any time (before,after).

I know this works for IO calls, you'd have to check if it works when the F0005 triggers fire from another function.

To suppress a trigger you would need to perform whatever IO fires the trigger in a C function with either of the following API calls prior to it.

JDEDB_RESULT JDB_SetRequestTriggerOption(
HREQUEST hRequest,
JDB_TRIGGER_OPTION nTriggerOption,
JDB_TRIGGER_TYPE nTriggerType,
int nValue);

Or

JDEDB_RESULT JDB_SetUserTriggerOption(
HUSER hUser,
JDB_TRIGGER_OPTION nTriggerOption,
JDB_TRIGGER_TYPE nTriggerType,
int nValue);


Where

hRequest is the request returned from the JDB_OpenTable
hUser is the user returned from JDB_InitBhvr

JDB_TRIGGER_OPTION is one of
JDB_OPTION_INSERTTRIGGER
JDB_OPTION_UPDATETRIGGER
JDB_OPTION_DELETETRIGGER
JDB_OPTION_FETCHTRIGGER
JDB_OPTION_ALLTRIGGERS

JDB_TRIGGER_TYPE is one of
JDB_PRE_TRIGGER
JDB_POST_TRIGGER
JDB_CURRENCY_TRIGGER
JDB_ALL_TRIGGERS

nValue is
JDB_OPTION_OFF
JDB_OPTION_ON

For Example

/* Disable the table triggers */
dbReturn = JDB_SetRequestTriggerOption(
hRequest,
JDB_OPTION_ALLTRIGGERS,
JDB_ALL_TRIGGERS,
JDB_OPTION_OFF);


Hope this helps



XE - SP16 - Update 2
Oracle 8.1.7
Sun Solaris 8
 
Hi Trevor,

thanks for the very detailed answer. It is a little more complicated than I was hoping for, but I'll look into it, as you obviously know what your talking about. Unfortunately, the IO is being done from many and varied places, which is why I want to use the trigger to capture the IO, so I'm not sure I can implement your suggestion here. My F0005 example is just one of a number of tables I need to do this to.
Perhaps if I call a new C BSFN based on your reccomendations in the BEFORE trigger event??


One new question. Do you know how I can get a message back to an interactive user. I can send work centre messages, but what I'm after is something more immediate, to let them know something has happened (i.e. a workflow request for approval has been submitted). I've tried setting an error, and setting the status bar message, but nothing seems to have any effect.
I've read several posts, here and on the JDE KG, so I know you can't call an application from the trigger event. How about an external app, like WinPopUp? There must be some way...

Thanks a lot for your reply,
David

B733.2 sp16
RS/6000 and Oracle.
Citrix clients.
 
Hi again Trevor,

I've look through your reply in slightly more detail now, and thanks, I'm sure it will be useful.

However, what I'm after is not to block the trigger. I want the trigger to run and perform it's logic. What I want is for the trigger to be able to stop the normal IO if needed.

For example, the BEFORE_INSERT trigger runs, checking it's own security algorythm, then decides that the new record should not be written to the database (awaiting approval for example). I write a copy of the new record to a special table. The problem is, how do I stop the database insert? If I change the values of the key fields to NULL, then no update is performed, but I get errors in my log, and depending on the APPL, the user would get nasty red "Database write failed" type error messages.

Thanks,
David
 
Hmmm.

As you know there aren't any particularly interesting looking system functions in TER design like Suppress Update! So a different approach might be required.

Would it be feasible to create a NER that performs the security check then performs whatever action is necessary. Unfortunately you'd need to replace each update/insert statement with the new NER call.

Alternatively you could all the insert/update to occur but change the keys to some value other than blank that will insert/update successfully. Define a new System Code "XX" and use a next number to generate a unique Code. For inserts you could delete the newly inserted (modified) record in the post insert.


Sorry nothing else springs to mind.




Trevor


XE - SP16 - Update 2
Oracle 8.1.7
Sun Solaris 8
 
Hi Trevor,

Yes, you're right, TER System Functions are very sparse. Basically Workflow and Messaging, plus Set TER Error which doesn't seem to do anything. But maybe I just don't understand the JDE error handling very well. I know you can't call an APPL from a TER, but sending a message to the Work Centre doesn't seem very useful for an interactive user.

I could create a BSFN to do all IO for the file, but not really feasible to implement. The TER suites my needs really well, it's just these to annoying problems

What you suggested is what I am doing for the moment as a work around.
Insert> Change key values to dummies, then delete the record again on the After Insert event.
Update> Change all the fields back to there original values.
Delete> Change key values to dummies, insert the dummy record, the let it get deleted normally.
This works well enough, just creates extra IO.
The other solution I thought of, particularly for Insert or Delete, was to write the a dummy record with the same key values, but changing descriptions etc to a PENDING DELETE/INSERT type message. This has the advantage of supplying some feedback to the user, as well as blocking the key in case of next numbering getting a bit lost somewhere.

The other problem is trying to get a message back to interactive users. Using the normal JDE Messaging or Workflow, I can get messages in the work centre, but this is not very immediate. I need to get a message to the status bar, or an informational message displayed, or a popup window, to let the users know that the entry they just made has gone somewhere. As any Insert/Update/Delete that they do will not be reflected on their screen, I can imagine them trying many (probably thousands of) times to Insert/Update/Delete the same thing. Not really a problem from my point of view, but a waste of time for the user, and users don't like it when you waste their time. If I set it up on all of the Citrix boxes, I will try writing a BSFN to make an external call to Pop Up Message if I'm running on a Win32 machine. Might get a bit annoying if you run a batch update locally though :)

Anyway, thanks for your time and thoughts on this. Maybe this is an unusual situation, but I think the concept is very useful for many of the projects I've worked on in the past, for large multi-company corprations, that want to have control over changes made to certain core data elements (like UDC's and codes etc.). Very quick to implement, with only the 3 TER's per file to program, plus a simple transaction approval screen per file.

Cheers,
David
 
Hi David,

Just an addition to how to notify the user from TER.

- you can call BSFN from TER (I checked this possibility under B7332 too)
- you can call OW Form via Form Interconnect from BSFN (I checked this possibility under B7332 too)
- so call a BSFN (with the necessary identification parameters if you need it) and call you notification form from the BSFN (with the necessary identification parameters if you need it).

Although I haven't tried it yet but as far as I know it works.

Please, let us know if you tried it. Thanks.

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Hi Zoltan,

yes, I thought of that, but I haven't tried it yet. The reason is that there is
a SAR on the JDE KG for exactly that, and JDE saying that it doesn't/won't/can't
work. You can't call an APPL from a TER, even via a BSFN.

I might give a try anyway though, as it's simple enough to set up.

I've written a (very) simple Win32 .exe programme that pops up a message, which I'll try as well. I haven't figured out how to send the message I want displayed in the call yet, but the little window is cute :). Any ideas on how to instruct the CNC guys on how to employ a Win32 program? The very thought gives me nightmares at this site, with many pathcodes, and two different physical locations.

Thanks for your thoughts anyway.

David
 
For status bar text try a JDE business function that does.

jdeStatusBarSetText(((LPJDEAPP) GetWindowLong(lpBhvrCom->hDlg, DWL_USER))->lpStatBar, lpDS->szText, SBSECTION_TEXTA);

The only change you'll need to make is lpDS->szText to be whatever your DS parameter is.


For a popup message try a JDE business function that does.

#ifdef _WIN32
#ifndef IAMASERVER
MessageBox(0,lpDS->szText,lpDS->szCaption,0)
#endif
#endif

This code will only run if on a WIN32 platform if it isn't running on a server.
You can safely leave the first parameter to the MessageBox call as 0.
Parameter 2 is the text that appears in the box. Parameter 3 is the title for the message box. Parameter 4 lets you add an icon to the message box, exclaimation marks, question marks, etc. For parameter 4 you could try values of 0, 16, 32, 48 and 64.

I haven't tested this code so forgive me if it goes a little wonky.


XE - SP16 - Update 2
Oracle 8.1.7
Sun Solaris 8
 
Hi Trevor,

I did try setting the status bar text, but using the BSFN that is there, not using the API directly. It didn't work.

Your MessageBox code works brilliantly, and generally solves the problem. Thanks very much indeed.

The only (small) issues I have are:
1: can I make this window execute asyncronously? So that it doesn't stop later processing until OK is pressed.

2: Can I stop more than one message appearing at a time. For example, if I change 3 lines on a grid, press ok, then I get the message 3 times. Combined with point 1: above, one message would be enough.

Cheers,
David
 
David,

I tried the statusbar API in a business function and it seemed to work ok.

If you call the BF that calls the popup window from an asychronous event you can check the "Asychronously" box. Otherwise I'm not sure if you can prevent the code of stopping without going back to running the Message Box as a separate executable.

The MessageBox function is a standard windows function. If you have Microsoft C++ installed you might have MSDN (Microsoft Developers Network - I think) installed. MSDN has all sorts of "interesting" things in it.




Trevor

XE - SP16 - Update 2
Oracle 8.1.7
Sun Solaris 8
 
Trevor,

Did you try the statusbar API from a BSFN called from a TER, or ? I tried it using the BSFN, not the API directly, but nothing at all seemed to happen. I think there is some logic somewhere after the I/O that clears the status bar and any error messages, so anything I've set will be lost. Maybe if put the message into the After Insert, it would make it back.

I looked everywhere for the little check box, but it does not seem to be available for TER's. I've also play with the MB_APPLMODAL parameter, and setting the handle to NULL, but it doesn't do want I want.

I've been browsing through the MSDN info on-line, and it's helped a lot, so thanks. I've looked at using CreateDialog instead, and having only one window appear for multiple message, but it doesn't really work from the TER perspective.

Again, thanks for you suggestions. It's given me a nice, professional approach that works in the majority of situations. For the exceptions, I can probably code around in the TER. The biggest problem I can see would be for a UBE running locally, unless IAMASERVER works for that somehow, I can imagine having to sit there clicking the OK box several thousand times.

David
 
Hi David,

Would you please tell us the number of the SAR that you have mentioned. I am really curious to it.

On the other hand I would be happy to hear about your results if you tried the TER/BSFN/Form Interconnect way (in spite of the SAR :)

Regards,

Zoltán

P.S.: Trevor's posts are really valuable. I will surely remember it when such type of issue will arise here too.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Zoltán,

SAR: 1726778 Call an Appl from Table ER System H91
Status: 04 - Returned-Redesign not Planned

Document: ott-99-0065
Title: Table Event Rules and Application Calls
Abstract: Document explains why application calls cannot be made from within Table Event Rules (ER).
Release: B73.2, B73.3, Xe

"OneWorld does not support application calls from Table Event Rules. That is why there is no form interconnect option in Table ER. There is no way to call an application within TER, including using business functions. This feature is not supported in Table Event Rules."


Actually, that is about the weekest explanation I've seen. Why can't you? Because you can't. Hard to see why it was neccessary to create a special document for it.
I still haven't tried it yet, as the MessageBox code in a C BSFN that Trevor suggested works really well, if not better than an APPL for now.

David
 
David,

I don't think it is so much a question of a week explanation - as they need
to explain the pitfalls of why not to run forms within TERs.

Frankly, I would find it quite frightening if I discovered one of our
developers was building interactive (user drawn) logic into TERs. Tables
are updated many ways - including batch applications. There is no where to
send the form, from within a UBE...

Of Course, you could put logic within your TERs to determine if a specific
application updated the row, but table updates are supposed to be screaming
fast. You lock the row(or table?) the row is locked during that update
phase... If the user walks away and comes back a year later... corruption,
mafia style (you may never know what hit the table and subsequent ER within
the TERs will go away).

I define Interactive Logic - as something a user has to respond to.



Daniel Bohner
[email protected]
www.existinglight.net
JDE - XE & AS/400
JDE - B7331 & MS SQL 7x
 
Re: RE: Table Event Rules (TER) Again

Daniel,

I take your point, and generally agree.

My comment on it being a weak explanation was simply because the document titles and description indicates it gives a reason, when the only reason given is, that you can't.

It is possible to determine that the function is running on Win32, and that it is not a server, within the C code BSFN. I can also check that the request is not coming from a UBE.

What I'd like is simply to pass back a status message that action was taken by the TER as feed back. As an alternative to that not working, I'd like to start a simple APPL asynchronously that gives the feedback. My only alternative that actually works within the JDE framework is to call the C MessageBox function, which I haven't figured out how to do asynchronously yet, so yes, it does slow down the process, and potentially lock records. There is no user drawn logic with the TER, it is simply to give feedback that the TER has done something that they need to be aware of. I can send a work centre message, but there is nothing to force them to display this.

On the other hand, I can think of several possible reasons why I would want to start user drawn logic with the TER to capture data from the user. Still, all of them would ideally be called asychronously, or at least called from the post-processing event.

Like all deveopment work, it is really a question of what is appropriate for required task.


Independant Technical Consultant
CNC and Development
Currently in Europe on B732sp16 RS6000 Oracle
 
RE: RE: Table Event Rules (TER) Again

Robert,

I put some thought into this - and here's a suggestion.

Clone the table that deletes/updates will occur within. This will be your
tag/event table.

Create a BSFN (and it doesn't have to be a C Function - NER) that simply
copies the record into the tag/event table.

Within the Table's Delete ER(s) - push the record-2-B-Deleted into the
tag/event table (safe storage).

We used to do this in a home grown Jail Management System at ADA County.
Basically - if a delete is requested - it was copied to a temp file first
then deleted from the table. Then, a supervisor had to approve the delete.
If it isn't approved - then the supervisor could drop it right back into the
table.... The NER could email 'someone' and let them know that the
tag/event table has entrees.

I'd think that a similar process would find itself very beneficial in your
circumstance?





Daniel Bohner
[email protected]
www.existinglight.net
JDE - XE & AS/400
JDE - B7331 & MS SQL 7x
 
Re: RE: RE: Table Event Rules (TER) Again

Hi Daniel,

actually this exactly what I'm trying to do.

The question was to do with preventing the original table being changed, and with sending a message back to the user that the insert/update/delete had been diverted. Unlike your home grown Jail Management System, I don't want to make the change to the database until the it is approved.

The C function is not needed for writing to the new table, and it's actually less work to do it directly from within the TER. I use a C function only for the message itself.

Emailing the user is quite straight forward, but there is no way to make sure they get the message, certainly not immediately. Workflow will handle notifying the appropriate administrator for approval.

Regards,
David

Independant Technical Consultant
CNC and Development
Currently in Europe on B732sp16 RS6000 Oracle
 
Back
Top