Suppose you want to check the user permissions on inserting or updating the records in Oracle Forms, then you can use Pre-Insert and Pre-Update triggers for that particular data block to check whether user is having proper permission or not.
The example is given for HR schema and the following demo table is created for this:
Create Table User_Permissions (User_Name varchar2(50) Primary Key,
Can_Insert Varchar2(1) default 'N',
Can_Update Varchar2(1) default 'N')
Insert into User_Permissions values ('FORMUSER1', 'Y', 'N');
Commit;
Below is the screen shot for the examle:
You can download this form from the following link: Pre-Update-Insert.fmb
The following is the code written in Pre-Update trigger to check the permissions from the database:
Declare
n_allow number;
begin
Select 1 into n_allow
from hr.user_permissions
where user_name = 'FORMUSER1'
and can_update = 'Y';
--- all is well if no exception raised else stop in exception area
exception
when others then
message('You have no permission to update the record.');
message('You have no permission to update the record.');
raise form_trigger_failure;
end;
The following is the code written in Pre-Insert trigger to check the permissions from the database on Insert:
Declare
n_allow number;
begin
Select 1 into n_allow
from hr.user_permissions
where user_name = 'FORMUSER1'
and can_insert = 'Y';
--- all is well if no exception raised else stop in exception area
exception
when others then
message('You have no permission to insert the record.');
message('You have no permission to insert the record.');
raise form_trigger_failure;
end;
n_allow number;
begin
Select 1 into n_allow
from hr.user_permissions
where user_name = 'FORMUSER1'
and can_insert = 'Y';
--- all is well if no exception raised else stop in exception area
exception
when others then
message('You have no permission to insert the record.');
message('You have no permission to insert the record.');
raise form_trigger_failure;
end;
The code is written for Save button:
Commit_form;