CREATE OR REPLACE TRIGGER TRG_calcDeposit
BEFORE deposit ON TRG_USER_INFO FOR EACH ROW
DECLARE
V_STATT TRG_USER_STATUS.STATUS%TYPE;
BEGIN
SELECT STATUS INTO V_STATT FROM TRG_USER_STATUS
WHERE USERID = :OLD.USERID;
IF V_STATT = 'A' THEN
   RAISE_APPLICATION_ERROR(-20001 ,'CANNOT deposit FROM USER_INFO TABEL.');
ELSIF V_STATT = 'R'  THEN
    DEPOSIT FROM TRG_USER_STATUS
    WHERE USERID = :OLD.USERID;
END IF;
END;  

create table trg_deposit_details(depositid Number not null primary key, 
holdername varchar(20) not null, depAmt Number not null, 
noofdays Number not null, depositDate Date Not null);

create table trg_deposit_status(depositid number 
CONSTRAINT fk_depositid REFERENCES trg_deposit_details(depositid), 
matureDate Date Not null, matureAmt Number not null);

create or replace trigger trg_calcdeposit
before insert on trg_deposit_details for each row
declare 
int_amt trg_deposit_status.matureAmt%type;
maturedt trg_deposit_status.maturedate%type;
begin
if :new.noofdays <=30 then
	int_amt=:new.depAmt(1+2/100);
elsif :new.noofdays <=90 then
	int_amt=:new.depAmt(1+3/100);
else
	int_amt=:new.depAmt(1+4/100);
end if;

maturedate = :new.depositdate + :new.noofdays;

insert into trg_deposit_status (depositid,maturedate,matureamt)
 values(:new.depositid,maturedate,int_amt);
end;

insert into trg_deposit


=========================================================================================

create table mob_service_details
(serviceid number primary key
,service_name varchar2(50) not null
,plan number not null);

create table mob_connection_details
( mobile_num number primary key
,cust_name varchar2(50) not null
,serviceid number not null
,city varchar2(50) not null
, doc date not null
,dob date not null
constraints fk_service_id 
foreign key(serviceid)
references mob_service_details(serviceid));

9964212345,'james',1,'mysore','15-jan-2002','12-sep-1985'

select sysdate-dob from mob_connection_details where upper(cust_name)='JAMES';

insert into mob_service_details (serviceid,service_name,plan) values (1,'vodafone',299);
insert into mob_service_details (serviceid,service_name,plan) values (2,'vodafone',599);
insert into mob_service_details (serviceid,service_name,plan) values (3,'vodafone',399);
insert into mob_service_details (serviceid,service_name,plan) values (4,'airtel',1099);
insert into mob_service_details (serviceid,service_name,plan) values (5,'airtel',299);
insert into mob_service_details (serviceid,service_name,plan) values (6,'airtel',599);
insert into mob_service_details (serviceid,service_name,plan) values (7,'vodafone',3999);
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9894512345,'harry',1,'chennai','15-jan-2006','12-mar-1982');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9884522345,'luzzi',2,'madurai','15-may-2005','12-apr-1979');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9984412345,'massa',3,'hyderbad','15-jan-2007','12-may-1983');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9884212345,'button',2,'bangalore','15-apr-2008','12-jun-1975');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9964212345,'james',1,'mysore','15-jan-2002','12-sep-1985');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9793512345,'mathew',4,'pondi','15-may-2003','12-jan-1989');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9686512345,'john',6,'bangalore','15-may-2007','15-sep-1985');
insert into mob_connection_details (mobile_num,cust_name,serviceid,city,doc,dob) values (9843512345,'marry',7,'mangalore','15-may-2008','12-jan-1984');

select cust_name from mob_connection_details  
where serviceid in (select serviceid from mob_service_details
			where service_name='vodafone'
			and plan=299);

select customer_name from mob_connection_details  where (sysdate-doc)>1000;

update mob_connection_details  set city='bengaluru' where city='bangalore';

delete from mob_connection_details  where city like 'p%';

==========================================================================================================

create or replace procedure calculateticketamount
(theater_name in varchar, movie_name in varchar, lang in varchar, qty in number, result out number)
check2 number;
check1 varchar2(1);
amt number;
begin
if qty<=0 then
	result=-1;
end if;
select count(*) into check2 from movie_details
where theatername=theater_name
and   moviename=movie_name
and language=lang;
if check2 = 0 then
	result=-2;
end if;
select moviestatus into check1 from movie_details
where theatername=theater_name
and   moviename=movie_name
and language=lang;
if check1='C' then
	result=-3;
end if;
if check1='R' then
	select qty*ticketprice into amt from movie_details
	where theatername=theater_name
	and   moviename=movie_name
	and language=lang;
end if;
result=amt;
end;

declare
result number;
begin
calculateTicketAmount('PVR','ayan','english',-1,result);
end;

declare
result number;
begin
calculateTicketAmount('PVR','ayan','tamil',5,result);
end;

==================================================================================