Slip 10 - C) Consider the following entities and their relationships. Gym (Name, city, charges, scheme) Member (ID, Name, phoneNo, address) Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will accept member id and scheme from user and display charges paid by that member. 2) Write a trigger which will fire before insert or update on Gym having charges less than 1000. (Raise user defined exception and give appropriate message)

Solution:

SQL> create table gym(gno int primary key,gname varchar(20),city varchar(20),charges int,scheme varchar(20));

SQL> create table member(mid int primary key,mname varchar(20),phone int,addr varchar(20),gno int);

SQL> insert into gym values(101,'aaa','pune',1000,'xyz');

SQL> insert into member values(001,'mmm',1122334455,'goa',101);

1)Write a function which will accept member id and scheme from user and display charges paid by that member.

SQL>  set serveroutput on

SQL> create or replace function f3(abc in varchar) return number as xyz number;

  2   begin

  3  select gym.charges into xyz from gym,member where gym.gno=member.gno and mid='2';

  4   if sql %found then

  5  return(xyz);

  6   else

  7  return null;

  8  end if;

  9  end f3;

 10   /

Function created.

SQL> begin

  2  dbms_output.put_line('gym-'||f3 ('2'));

  3  end;

  4  /

gym-2000

PL/SQL procedure successfully completed.

2)Write a trigger which will fire before insert or update on Gym having charges less than  1000. (Raise user defined exception and give appropriate message)

SQL> set serveroutput on

SQL> create or replace trigger t1 before insert or update on gym

  2  for each row

  3  begin

  4  if(:new.charges<=1000)then

  5  raise_application_error(-20001,'charges>1000');

  6  end if;

  7  end;

  8  /

Trigger created

SQL> insert into gym values(104,'ddd','pune',-1000,'mmm');

insert into gym values(104,'ddd','pune',-1000,'mmm')

            *

ERROR at line 1:

ORA-20001: charges>0

ORA-06512: at "SCOTT.T1", line 3

ORA-04088: error during execution of trigger 'SCOTT.T1'.