Write
the database trigger on account that checks following:-
1)
The account number
which truncation is being performed is valid account number.
2)
The transaction Amount is not zero and it is Positive
3)
In case of withdrawal the amount does not exceed the current balance for
that account number
account:-
ANO NAME BALANCE CITY
----------
---------- ---------- ----------
101 renuka 10000 chikhali
102 krishna 9000 valod
103 mansi 8000 bardoli
104 rachna 7000 surat
105 sevali 6000 chikhali
INPUT
create
or replace trigger tri1 before insert or update on account for each row
declare
curbal number(6);
Cur_ano number(5);
min_bal number(5);
begin
min_bal:=500;
if :new.ano=0 then
raise_application_error(-20000,'the
account number is not valid');
end if;
if :new.balance <=0 then
raise_application_error(-20001,'the
balance cannot be zero or negative');
end if;
if :new.balance<=min_bal then
raise_application_error(-20002,'the
balance cannot be less than 500');
end if;
end;
/
1) insert
into account values(106,'sevali',200,'surat');
2) insert
into account values(100,'sevali',0,'surat');
3) insert
into account values(100,'sevali',0,'surat');
4) insert
into account values(100,'sevali',7000,'surat');
5) update
account set balance=balance-6000 where ano=105;
6) update
account set balance=balance-600 where ano=105;
OUTPUT
Trigger created.
1)
ERROR at line 1:
ORA-20002: the balance cannot be less
than 500
ORA-06512: at
"126370307018.TRI1", line 14
ORA-04088: error during execution of
trigger'126370307018.TRI1'
2)
ERROR at line 1:
ORA-20000: the account number is not
valid
ORA-06512: at
"126370307018.TRI1", line 8
ORA-04088: error during execution of
trigger '126370307018.TRI1'
3)
ERROR at line 1:
ORA-20001: the balance cannot be zero
or negative
ORA-06512: at
"126370307018.TRI1", line 11
ORA-04088: error during execution of
trigger '126370307018.TRI1'
4)
1 row created.
5)
ERROR at line 1:
ORA-20001: the balance cannot be zero
or negative
ORA-06512: at
"126370307018.TRI1", line 11
ORA-04088: error during execution of
trigger '126370307018.TRI1'
6)
1 row updated.
account:-
ANO NAME BALANCE CITY
---------- ---------- ----------
----------
101 renuka 10000 chikhali
102 krishna 9000 valod
103 mansi 8000 bardoli
104 rachna 7000 surat
105 sevali 5400 chikhali
100 sevali 7000 surat
0 Comments