Trigger is collection of SQL , PL/SQL and Form coding statements executed based on the event.
Triggers are executed automatically at run time based on an event.
Triggers can be created in three levels.
Form level triggers
Block level triggers
Item level triggers
Form Level Triggers
These triggers are applied to all the components in the form.
Example
Slno
Trigger
Explanation
1
pre-form
Activated before entering into the form.
2
when-new-form-instance
Activated whenever the form is ready to accept data from user
3
on-clear-details
Activated when form is cleared ( clear screen )
4
post-form
Activated when cursor leaves the form.
5
on-logon
fires when user enters into application.
This is the first trigger executed in forms.
Application loses database connection as soon as on-logon triggers is executed.
User has to provide database connection information in this trigger code to re-establish the connection
6
on-logout
Fired when user quits the application
This is the last trigger executed in forms
7
when-timer-expired
fired whenever there is a change in system time
Block level triggers
Applied to all items in the block. Example
Slno
Trigger
Explanation
1
pre-block
Fired before entering into the block.
2
when-new-block-instance
Fried when block is ready to accept input.
3
when-clear-block
Fired whe block is cleared.
4
pre-query
Fired when executing a query.
5
post-query
Fired after executing the query.
6
post-block
Fired when cursor leaves the block
7
when-create-record
Fired whenever a new record is inserted.
Item level triggers
Applied for a particular item.
Slno
Trigger
Explanation
1
pre-text-item
Fired before entering into the text item
2
post-text-item
Fired when cursor moves to the next item
( Fired after placing cursor in next item )
3
when-new-item-instance
Fired when item is ready to accept input
4
when-validate-item
Fired when item value is changed
5
when-clear-item
Fired when item is cleared
6
key-next-item
Fired when cursor is moving to next item.
(Fired before placing cursor in next item )
Cursor will not be placed in next item after executing this trigger. ( Use next_item built_in )
Fired only when TAB key is pressed.
Button
Slno
Trigger
Explanation
1
when-button-pressed
Fired when button is pressed
Radio group
Slno
Trigger
Explanation
1
when-radio-changed
Fired whenever radio group value is changed
Check box
Slno
Trigger
Explanation
1
when-checkbox-changed
Fired whenever checkbox value is changed
List Item
Slno
Trigger
Explanation
1
when-list-changed
Fired when list value is changed
Lets workout with few examples Example of on-logon trigger
Create a table student.
SQL> create table student ( sno number(3),
Sname varchar2(10),
Marks number(3));
Insert two rows into the table.
SQL> insert into student values ( 101 , ARUN, 40);
SQL> insert into student values ( 102 , KIRAN, 60);
SQL> commit;
Now, create form based on student table ( using data block and layout wizard )
Save the from with the name STUDENT.FMB
In object navigator of STUDENT.FMB
Create a new trigger under STUDENT form.
Select trigger click on create button
Select ON-LOGON Ok
Write the following code in the trigger
Message (Welcome to my Application);
Logon (scott, tiger@oracle);
Click on compile button.
You should get a message Successfully compiled
Message ( ) -- is a built_in, used to display message
Logon ( ) -- is a built_in, used to extablish database connection through code.
Note: Application loses the database connection as soon as ON-LOGON trigger is executed.
Hence, to re-establish the connection we use logon ( ) -- built-in
Similarly , Lets us create ON_LOGOUT trigger ( It is a form level trigger).
Create a new trigger under STUDENT form.
Select trigger click on create button
Select ON-LOGOUT Ok
Write the following code in the trigger
Message (Thank you for using my Application);
Click on compile button.
You should get a message Successfully compiled
So, we have created two triggers in our form.
Program Compile Module
Program Run Form
As we know, ON-LOGON trigger is the first trigger which is fired,
We get a message Welcome to my Application at the bottom of the form.
Select Action in Menu Bar Exit , ON-LOGOUT trigger is executed.
Now, lets make few changes in the existing form ( STUDENT.FMB)
Open the existing form ( STUDENT.FMB ) Open ON-LOGON trigger ( Right click Pl/SQL editor )
Include another message( ) built_in
Now, Compile.
Close the trigger.
Program Compile Module
Program Run form
We get welcome message in pop up window.
By using additional message( ) built_in , we get message in pop up window.
Example of WHEN-VALIDATE-ITEM trigger ( Trigger fires when item value is changed).
Create a table EMPONE in our database ( It is same as standard EMP table).
Create a form based on EMPONE table ( I think you already have it. EMPONE.FMB ).
If you already have EMPONE.FMB, Open the existing form ( EMPONE.FMB)
In Object Navigator ,
Under data block (EMPONE) Item ( ENAME) Create trigger WHEN-VALIDATE-ITEM
Write the following code in WHEN-VALIDATE-ITEM trigger
begin
if :ename is null then
message ('Employee name cannot be blank');
message(' ');
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message successfully compiled.
Lets create another trigger ( WHEN-VALIDATE-ITEM )which checks for valid salary.
In Object navigator under data block ( EMPONE) under SAL item Create trigger WHEN-VALIDATE-ITEM
begin
if :sal <3000 then
message('Employee sal must not be less than 3000');
message(' ');
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message successfully compiled.
Raise form_trigger_failure :
It stops the user action. Cursor cannot be moved to next item until restriction is satisfied.
It is similar to raise_application_error in database trigger.
Note
Items are referred with : ( COLON ) before them in coding.
So, we have create two trigger. Both are Item level triggers.
One trigger in ENAME item.
Another trigger in SAL item.
Program Compile Module
Program Run Form
In the form run time environment ( Oracle Application Server Form Services )
Enter Empno 101
Keep the cursor on ENAME ( do not enter anything)
Keep the cursor on JOB item, we get a message Employee name cannot be blank
Click on OK Cursor will be back on ENAME item.
That is the advantage of raise form_trigger_failure.
Cursor cannot be moved to next item until restriction is satisfied.
Lets check the trigger on SAL Item.
Enter SAL value 2000, we get message Employee salary must not be less than 3000.
Order of Execution of triggers at runtime Hierarchy of Form level triggers
click on create button
