Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: 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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Seems to be the same issue (with partially using my suggestion) as in this thread: http://www.dbforums.com/oracle/16586...r-problem.html 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?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •