SECTION-C:
Triggers :
Solve the following query in PL/ SQL using Triggers.
(1)Create a table : transaction ( Item_no., Item_name, Rate_Perunit, Qty, Total)
Write a database trigger to perform : whenever a row in updated or inserted with entry (item no, item
name, rate, qty). The total is calculated and entered into the total field of the record.
Ans: Create table Transaction(Item_no number(5), Item_name varchar2(15), Rate_Perunit number(5),
Qty number(5), Total number(5));
insert into Transaction values(3,'xyz',12,9,108);
insert into Transaction values(4,'nihfg',30,2,60);
Create or Replace Trigger Trans After Insert on Transaction For Each Row
Declare
mtotal number(5);
mqty number(5);
mrate number(5);
Begin
select rate_perunit,qty into mrate,mqty from Transaction where item_no=:Old.item_no;
mtotal := mrate*mqty;
update Transaction set total=mtotal where item_no=:Old.item_no;
end;
(2) Create tables : itemmst (item_no, name, rate)
Tran (item_no, qty, total)
Whenever tran table is updated or inserted with row values (item_no, qty). The total amount is
calculated and perform the necessary changes, use trigger for the above.
Ans: Create table Itemmst(Item_no number(5) primary key ,Name varchar2(15),Rate number(4));
Create table Tran(Item_no number(5) references Itemmst(Item_no),Qty number(5),Total number(5));
insert into Itemmst values(1,'rack',20);
insert into Itemmst values(2,'bat',15);
insert into Itemmst values(3,'ball',1);
insert into Tran values(2,15,0);
insert into tran values(3,7,1);
Create or replace Trigger Transmst After Update or Insert on Tran referencing OLD AS old For Each Row
Declare
mtotal number(5);
mqty number(5);
mrate number(5);
mitemno number(5);
Begin
Select Qty,Item_no into mqty,mitemno from Tran where Item_no=:OLD.Item_no;
Select Rate into mrate from itemmst where Item_no=mitemno;
mtotal:= mrate*mqty;
Update Tran set Total=mtotal;
End;
(3) Write a program to access the name of employee for a particular given empno. If there is no
employee for given empno, generate no_data found exception and insert the appropriate message and
empno into the temp file.
Ans:
Create table Emp1(Empno varchar2(6),Empname varchar2(15));
insert into emp1 values('e001','aarti');
Create table Temp1(Msg varchar2(50),Empno varchar2(6));
desc temp1;
select * from temp1;
Declare
mempno varchar2(20);
mename varchar2(15);
Begin
mempno:=:mempno;
Select Empno,Empname into mempno,mename from Emp1 where Empno=mempno;
dbms_output.put_line(mempno || mename);
insert into temp1 values('data found',mempno);
Exception
When No_Data_Found Then
dbms_output.put_line('no data fonud');
insert into temp1 values('not exist',mempno);
End;
select * from temp1;
(4) Write a program which calculate the ratio of X and Y cols. of the RATIO table, if the ratio is greater
than 0.72, then it inserts the ratio into result table otherwise does nothing, Handle the case when
denominator is 0 (zero).
Ratio (samp_id, X,Y)
Result_table (sample_id, ratio)
Create table Ratio(Samp_id number(6) primary key,X number(4),Y number(4));
insert into ratio values(1001,25,50);
insert into ratio values(1002,78,10);
select * from Ratio;
Create table Result(Samp_id number(6) ,Ratio_cal number(6,2));
Declare
Divide_By_Zero Exception;
mcount number(5);
mX number(4);
mY number(4);
mSamp_id number(6);
mRatio number(6,2);
Begin
Select count(Samp_id) into mcount from Ratio;
mSamp_id:=1000;
For i IN 1..mcount
Loop
Select X,Y into mX,mY from Ratio where Samp_id=mSamp_id+i;
If mY=0 then
Raise Divide_By_Zero ;
End IF;
mRatio:=mX/mY;
if mRatio>0.72 then
dbms_output.put_line(mRatio);
insert into Result values(mSamp_id,mRatio);
End if;
End Loop;
Exception
When Divide_By_Zero then
dbms_output.put_line('divide by 0 not allowed');
End;
select * from Result;
(5) Write a program that modifies the Accounts table based on instructions stored in the ACTION table.
Each row of the ACTION table contains an account number to act upon, an action to be taken (insert,
update or delete) and an amount to update the account to if the action is not a delete. On an insert, if
the account already exist, an update is performed instead. If the account does not exist then the
account is created via an insert. On delete, if the row does not exist, no alternative action is taken.
Accounts (account_id, balance)
Action (account_id, oper_type, new_value)
Ans: Create table Accounts(Account_id number(6) primary key,Balance number(7));
Create table Action(Account_id number(6) references Accounts(Account_id), oper varchar2(10),
new_value varchar(15));
select * from accounts;
insert into action values(4,'Insert',7997);
Create table Action(Account_id number(6) primary key, oper varchar2(10), new_value varchar(15));
insert into accounts values(1,10000);
Create or replace trigger Acc After Insert or Update or Delete on Accounts referencing OLD AS old For
Each Row
Declare
moper varchar2(10);
maccountid number(6);
mvalue number(6);
mbalance number(7);
Begin
maccountid:=:OLD.Account_id;
Select oper, new_value into moper,mvalue from Action where Account_id=maccountid;
If moper='Insert' then
Insert into accounts values(maccountid,mvalue);
ElsIf moper='Update' then
Update Accounts set Balance=mvalue where Account_id=maccountid;
ElsIf moper='Delete' then
Delete from Accounts where Account_id=maccountid;
End if;
End;
drop trigger Acc;
select * from action;
