|Receive Email for New Articles|
|Triggers in Oracle HRMS and Payroll||| Print ||
|Written by Anil Passi|
|Wednesday, 21 November 2007|
When it comes to Oracle HRMS or Oracle Payroll, creating triggers on tables is a different preposition.
This is also one of the least documented areas of Oracle HRMS.
Hence in this article, I will touch base upon the basics of creating triggers on Oracle HRMS and Oracle Payroll tables.
In the article that follows this, I will give you a step by step audio-video demo of creating triggers in Oracle HRMS.
What is a Database Trigger?
It is a piece of code that gets executed when changes happen to the data in the table.
The main benefit of trigger is that you get both the old value and also the new value of the record being changed.
Is creating triggers a good practice?
Absolutely not - in Oracle Apps.
You should instead prefer below in priority order listed
1. A business event [if available ] –Best Practice
2. An API Hook [if available]
3. Extension using forma personalization or OA Framework extension [ if possible ]
4. Use CUSTOM.pll [ if applicable ]
5. Extend workflow [ if applicable ].
Why is creating triggers not a good practice?
Because the code written in database triggers is a hidden/invisible code.
Yet, sometimes you have no choice but to create database triggers.
Triggers gain further importance when your logic depends upon both the old and the new value.
Why are triggers in HRMS & Payroll different from creation of triggers in other modules/tables?
Simple answer is date-tracking. Please see the related date-tracking article to understand that concept.
What would happen if I were to create a database trigger on Oracle HRMS table?
Assuming you have read the date-tracking article, read below.
If user makes change to Person name from Passian to Passi, in “update-mode”, then following will happen, assuming person id is 1000
Step 1. Existing record will be end-dated. In this case, the old value of the name , i.e. “Passian” will be retained
Step 2 .A new record will be created with value “Passi”
Effectively two records will be modified/created.
If in case, users were to change the record in CORRECTION-MODE, then just one record will be modified.
As you notice, depending upon how user modifies the record, either 1 record will be modified or two records will be modified.
Hence potentially the database trigger will fire twice. Also, the old & the new value will be the same for the record that gets end-dated.
This explains why you should avoid creating triggers on Oracle HRMS and Oracle payroll tables
How do we overcome this limitation?
This becomes possible by using the Database Trigger screen in Oracle HRMS.
Perhaps in a future article, I will provide an audio-visual demo to give you a live demo.
Database triggers in HRMS can be created from the Trigger Generation.
Benefits of creating trigger through the screen are:-
1. You trigger code will be fired just once, depending upon whether users modifies record in UPDATE or CORRECTION mode.
2. The old and the new values passed to the trigger will be accurate regardless of UPDATE or CORRECTION mode for data changes.
Below image shows the screen using which HRMS Triggers can be generated.
Navigation for Trigger Generation
written by Debojyoty Sadhukhan , November 22, 2007
written by khurshid , November 23, 2007
written by Jason , November 25, 2007
SO to PO (transfering attachments) with no trigges?
written by ChitraG , November 26, 2007