If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Trigger and function problem

Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 07-25-10, 22:05
Tanmoy Saha Tanmoy Saha is offline
Registered User
 
Join Date: Apr 2010
Posts: 4
Trigger and function problem

here is my tables{

drop table room;
create table room(
room_no integer not null,
catagori varchar(20) not null,
quality varchar(20) not null,
price integer not null,
primary key(room_no)
);

set serveroutput on;
create or replace procedure room_av(
roomno room.room_no%type,
catagoris room.catagori%type,
qua room.quality%type,
pri room.price%type) is
begin
insert into room (room_no,catagori,quality,price) values (roomno,catagoris,qua,pri);
commit;
end room_av;
/
begin
room_av(102,'single','2nd_class',200);
room_av(103,'single','2nd_class',200);
room_av(104,'single','2nd_class',200);
room_av(105,'single','2nd_class',200);
room_av(202,'single','1st_class',300);
room_av(203,'single','1st_class',300);
room_av(204,'single','1st_class',300);
room_av(205,'single','1st_class',300);
room_av(302,'double','2nd_class',250);
room_av(303,'double','2nd_class',250);
room_av(304,'double','2nd_class',250);
room_av(305,'double','2nd_class',250);
room_av(402,'double','1st_class',400);
room_av(403,'double','1st_class',400);
room_av(404,'double','1st_class',400);
room_av(405,'double','1st_class',400);
room_av(406,'double','1st_class',400);


end;
/

drop table customer_info;

create table customer_info(
first_name varchar(20) not null,
last_name varchar(20) not null,
room_no integer not null,
catagori varchar(20) not null,
quality varchar(20) not null,
price integer not null,
date_open date not null,
primary key(room_no)
);

set serveroutput on;

--TRIGGER SECTION
create or replace trigger cust_room
before INSERT ON customer_info
for each row
declare
var customer_info.room_no%type;


BEGIN
var:=0;
var:=chcek1(:new.room_no);
if var!=0 then
SELECT catagori, quality, price
INTO :new.catagori, :new.quality, :new.price
FROM room
WHERE room_no=:new.room_no;
delete from room where room_no=:new.room_no;

elsif var=0 then
dbms_output.put_line('The room is not available');
end if;
END cust_room;
/
show errors

--FUNCTION SECTION

create or replace function check1(var number) return integer is
var1 integer;

begin
var1:=0;
select room_no into var1 from room where room_no=var;
return var1;
end;
/

i an facing problem with the trigger. plz give me a solution how i can do this
Reply With Quote
  #2 (permalink)  
Old 07-25-10, 22:30
anacedent anacedent is offline
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 5,043
>i an facing problem with the trigger. plz give me a solution how i can do this
Please clarify EXACTLY what "problem" you face.

BTW, you have a typo error in posted code.
>var:=chcek1(:new.room_no);
not as above but as below
var:=check1(:new.room_no);

>dbms_output.put_line('The room is not available');
line above makes NO sense, because PL/SQL runs deep inside RDBMS engine & no user will ever see it.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
Reply With Quote
  #3 (permalink)  
Old 07-25-10, 23:22
flyboy flyboy is offline
Registered User
 
Join Date: Mar 2007
Posts: 271
Seems to be the same issue (with partially using my suggestion) as in this thread: Trigger problem I just wonder why you do not follow in it.

There are some differences. Firstly, you decided not to describe the 'problem' at all. Hm, how do you expect anybody to guess it?
Secondly, my solution contained treating of NO_DATA_FOUND exception, which is raised, when SELECT INTO does not return any row. It will be raised in every SELECT (so the CHECK function is useless). It is however correct not to catch it, when you want the program to raise ORA-00001 exception. Again, as you did not post exact requirements on code, it might be ok. Please, consult PL/SQL User's Guide and Reference, available e.g. online on http://tahiti.oracle.com/
And, lastly, the client may not see the message printed via DBMS_OUTPUT (in sqlplus, SERVEROUTPUT may be set OFF). Please, consult the requirements, what shall be done. Is the front-end really in sqlplus?
Reply With Quote
  #4 (permalink)  
Old 07-26-10, 03:01
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 1,598
Quote:
Originally Posted by anacedent View Post
>dbms_output.put_line('The room is not available');
no user will ever see it.
Not true.

If you turn serveroutput on in SQL*Plus (or other tools that support this) and run a statement the fires the trigger you will see the message.
It's a nice debugging aid (but nothing more).
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On