Skip to main content

Hello.

I have to make an event on the TECHNICAL_OBJECT_REFERENCE_TAB table, an event which uses the AFTER CREATE, AFTER UPDATE and DELETE AFTER keywords.

My event uses the filter “NEW:LU_NAME=PartCatalog”, the action is execute online sql type, and I would need to know how I can identify if the user fires an INSERT / UPDATE / DELETE.

About INSERT, I guess the test “&OLD:TECHNICAL_CLASS IS NULL AND &NEW:TECHNICAL_CLASS IS NOT NULL” can do the stuff, but I don’t know how to know if the user fires an UPDATE or a DELETE, as both &OLD:TECHNICAL_CLASS and &NEW:TECHNICAL_CLASS have a value.

Thanks

There is no way to do that, that I know of, and I’ve looked for a way for a long time, as several events in my solution do have gateway logic based on whether it’s an insert update or delete.

 

Technically speaking, when you create an event that fires on “Insert/Update/Delete”, what happens is that IFS actually deploys 3 distinct triggers to your Oracle DB. Those triggers then call upon the associated actions. I.E., the event is not the actual trigger, the event is simply an object IFS uses to auto generate PLSQL Code that defines and deploys the required trigger to Oracle.

 

The easiest workaround there is is this:

 

  1. Duplicate your event, and have one of them specifically set to only Delete. the other one can be set to both insert and update because as you said, the NEW/OLD Attributes will be able to let you determine if it is an update or an insert.
     
  2. If your PLSQL Code Block logic is directly in the online sql action (not particularly recommended as this makes debugging way more difficult, no linter/auto complete etc), simply have the logic of the actions behave differently
     
  3. If your Online SQL Action instead simply calls for a custom PLSQL Method which holds all the logic (preferred option as then you only have 1 method to debug within PLSQL developper rather than two methods to debug in the Frontend), simply have your online SQL action create a variable such as this:

          

 

  1. Based on the example above, I have a duplicate event and event action set specifically to trigger on “Delete”, which instead sets this “trigger_type” to ‘DELETE’, but they both call the same method (holiday_request_notification) in either case.
     
  2. I then have my holiday_request_notification method behave differently based on the trigger_type argument parsed within the attribute, which allows all the logic to be handled knowing whether the user triggered an Insert Update or Delete :)

 

Hope that helps !

 

 


@romsar I guess you can spot an UPDATE by checking the objversion. Normally, each UPDATE should put a new timestamp in the objversion column.


@romsar I guess you can spot an UPDATE by checking the objversion. Normally, each UPDATE should put a new timestamp in the objversion column.

Note that IFS code, at least in Apps 10 (haven’t checked in Cloud), truncates the event Objversion to only be the date, and omits the time part of it.

 

If you look for ROWVERSION OLD and and NEW, they’ll be the same if the change happens on the same day.

 

 

However, OLD:ROWKEY will always be null for Inserts, and NEW:ROWKEY will always be not null for updates and deletes :)

 

Also, if you add any NEW attribute (such as NEW:ROWVERSION) to the event attributes on a DELETE Type event, IFS hardcodes that to the OLD attributes (such as OLD:ROWVERSIONà even if you are doing a delete (meaning both OLD:ROWERSION and NEW:ROWVERSION will be the same in a DELETE Event, or really ANY OLD/NEW attributes will be the same)

 

 

Being absolutely sure to spot DELETE from UPDATE does require a different event as I have highlighted above.

 


Reply