Forms

Trapping Errors Create the following tables in the database
create table bankmaster ( accno number(4) primary key,
accname varchar2(10),
acc_type char(1) check ( acc_type in ( 'S', 'C', 'R')),
currbal number(12,2) ,
opendate date);
create table transaction ( accno number(4) references bankmaster (accno),
ttype char(1),
trandate date,
amt number(12,2));

Open the form builder tool and create a new form based on bankmaster table by using data block wizard and layout wizard.
Save the form with the name BANKMASTER.fmb
In the Object navigator, under BANKMASTER data block, create ON-ERROR trigger
Provide the following code an compile.
declare
errcode number := ERROR_CODE;
dbmserrcode number;
dbmserrtext varchar2(200);
begin
if errcode = 40508 then
dbmserrcode := DBMS_ERROR_CODE;
dbmserrtext := DBMS_ERROR_TEXT;
if dbmserrcode = -00001 then
message ('Duplicate Account number not allowed');
message(' ');
elsif dbmserrcode = -2290 then
message ('Account type can be "S" or "C" or "R" only');
message(' ');
end if;
end if;
end;

Enter the following details
Accno -- 2
Accname -- chaitanya
Acctype -- H
Currbal -- 25000
Opendate -- 19-MAR-2013
Save.
We get an message as "Account type can be "S" or "C" or "R" only"

Note:
In the ON-ERROR trigger which we have created, we used the if condition based on the value -2290.
Actually -2290 is error which is raised at the database level for check constraint violation.
Look at the screen shot

Now, Go to Block , Clear the block and
Enter the following details
Accno -- 1
Accname -- vinay
Acctype -- S
Currbal -- 40000
Opendate -- 21-MAY-2013
Save.
We get an message as "Duplicate Account number is not allowed".

Note:
In the ON-ERROR trigger which we have created, we used the if condition based on the value -00001.
Actually -00001 is error which is raised at the database level when we insert duplicate value in primary key constraint.
Look at the screen shot

By trapping -00001 error, we are able to give meaningful messages at the form level.

In the ON-ERROR trigger which we have created, we used the if condition based on the value 40508
FRM-40508 , is the error raised when insert statement is failed in forms.
Now, let's create form based on transaction table using data block wizard and layout wizard.
save the form with the name TRANSACTION.fmb
Go to the layout editor and place to display items.
Name the display items as ANAME and BALANCE.

Create KEY-NEXT-ITEM trigger for the item ACCNO, provide the following code and compile.
begin
select accname, currbal into :aname, :balance from bankmaster
where accno = :transaction.accno;
next_item;
exception
when no_data_found then
message ('No such account holder exist when account no '||:transaction.accno);
message(' ');
raise form_trigger_failure;
end;

The above trigger gets name and balance into the display items.
Let's create ON-INSERT trigger at the block level, provide the following code and compile.
declare
a bankmaster.currbal%type;
begin
a := :balance;
if :ttype = 'W' then
:balance := :balance-:amt;
if :balance < 5000 then
:balance := a; -- replacing previous balance
message ('Not having minimum balance');
message( ' ');
raise form_trigger_failure;
else
update bankmaster set currbal = :balance
where accno = :transaction.accno;
insert into transaction values (:transaction.accno, :ttype, :trandate, :amt);
end if;
elsif :ttype = 'D' then
:balance := :balance+:amt;
update bankmaster set currbal = :balance
where accno = :transaction.accno;
insert into transaction values (:transaction.accno, :ttype, :trandate, :amt);
end if;
end;

The above trigger is fired when we insert the record.
It updates the current balance in bank master automatically.
Let's create WHEN-VALIDATE-ITEM trigger for the item trandate
provide the following code and compile.
begin
if to_date (:trandate) <> to_date (sysdate) then
message ('Enter todays date only');
raise form_trigger_failure;
end if;
end;

The above trigger make sure that we enter today's date only.
Save the form.
Compile the form.
Before running the form, lets insert some more data in table bankmaster by using following commands.
insert into bankmaster values ( 2,'vinay','C', 40000, '21-MAY-2013');
insert into bankmaster values ( 3,'sai', 'S', 45000, '15-FEB-2012');
insert into bankmaster values ( 4,'rupa', 'S', 50000, '09-AUG-2012');
commit;
Look at the current status of bankmaster and transaction tables in our database.

Now, let's Run the from.
Enter the Accno as 2 and click on tab
The KEY-NEXT-ITEM trigger will be executed and populates Accname and balance in the display items.

Continue entering the following details in the form
Acno - 2, Type - D Trandate -- todays date ( your current date ) , Amt -- 7000
Save.
Observe balance changes from 40000 to 47000 . ( ON-INSERT trigger will be executed.)

Go to Block --> Clear
Entering the following details in the form
Acno - 3, Type - W Trandate -- todays date ( your current date ) , Amt -- 20000
Save.
Balance changes from 45000 to 25000

Transactional Triggers

The following are the transactional triggers fired when DML operations are performed in database data block.
For the event INSERT
1) PRE-INSERT
2) ON-INSERT
3) POST-INSERT

For the event UPDATE
1) PRE-UPDATE
2) ON-UPDATE
3) POST-UPDATE
For the event DELETE
1) PRE-DELETE
2) ON-DELETE
3) POST-DELETE
Note
We have another trigger ON-COMMIT at form level.