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;

Save the form.
Compile the form.
Run the form.

Enter the following details

Accno -- 1
Accname -- karthik
Acctype -- S
Currbal  -- 10000
Opendate -- 17-FEB-2013

Save.

We get confirmation message " Transaction complete: 1 record applied and saved"

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

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

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.

 

Note

SYSTEM VARIABLES
ERROR_CODE	Holds the currently raised error number in forms
ERROR_TEXT	Holds the currently raised error message in forms
DBMS_ERROR_CODE	Holds the recently raised database error number
DBMS_ERROR_TEXT	Holds the recently raised database error message

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.