Slip 17 - C) Consider the following Item_Supplier database Company (name , address , city , phone , share_value) Person (pname ,pcity ) Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares i Constraints: name,pname primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a trigger before insert or update on No_of_shares field should not be zero.(Raise user defined exception and give appropriate message) 2) Write a function to display total no_of_shares of a specific person.

Solution:

1) Write a trigger before insert or update on No_of_shares field should not be zero. (Raise user defined exception and give appropriate message)

SQL> set serveroutput on 

SQL> create or replace trigger t3 before insert or update on com_per 

for each row 

begin 

if(:new.no_of_shares<=0)then

raise_application_error(-20001,'no_of_shares> O'); 

end if; 

end; 

/

Trigger created.

2) Write a function to display total no_of_shares of a specific person.

SQL> set serveroutput on SOL> create or replace function f2(xyz in varchar) return number as abc number; 

begin

select sum(person3.pno) into abc from company0 person3,com_per where companyo.cid=com_per.cid and person3.pno=com_per.pno and pname='ppp'; 

if sql %found then 

return(abc); 

else 

return null; 

end if; 

end f2; 

/

Function created. 

SQL> begin

dbms_output.put_line('company'||f2('ppp'));

end; 

/