Slip 29 - C) Consider the following entities and their relationships. Driver (driver_id, driver_name, address) Car (license_no, model, year) Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will display the total number of person who are using “Swift” car 2) Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriate message)

Solution:

SQL>create table employee(e_id int primary key,e_name varchar(10),address varchar(10));

SQL>insert into employee values(1,'reshma','koregoan');

SQL>create table investment(inv_no int primary key,inv_name varchar(10),inv_date varchar(10),inv_amount int,e_id int);

SQL>insert into investment values(1,'house','15thaug','50000',1);

1) Write a function which will return total investment amount of a particular client.

SQL> set serveroutput on

SQL> create or replace function fun1(abc in varchar)return varchar as xyz varchar(10);

  2  begin

  3  select sum(investment.inv_amount)into xyz from employee,investment where employee.e_id=investment.e_id and e_name='roma';

  4  if sql %found then

  5  return(xyz);

  6  else

  7  return null;

  8  end if;

  9  end fun1;

 10  /

Function created.

SQL> begin

  2  dbms_output.put_line('invamount-'||fun1('roma'));

  3  end;

  4  /

2) Write a trigger which will fire before insert or update on Investment having investment amount less than 50000. (Raise user defined exception and give appropriate message)

SQL>  set serveroutput on;

SQL>  create or replace trigger t2  before insert or update on investment

  2  for each row

  3   begin

  4  if(:new.inv_amount<=50000) then

  5   raise_application_error(-20001,'inv_amount>0');

  6   end if;

  7   end;

  8   /

Trigger created.