Results 1 to 5 of 5

Thread: Trigger

  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: Trigger

    here is my tables and trigger

    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)
    );

    --TRIGGER SECTION
    create or replace trigger cust_room
    before INSERT ON customer_info
    for each row



    BEGIN

    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;

    END cust_room;
    /
    show errors

    everything is working correctly. but i want to show a message like 'The room is not available' if no data found in room table without showing any error. what can i do?? please help

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    but i want to show a message like 'The room is not available' if no data found in room table without showing any error. what can i do?? please help
    Your frontend (i.e. your application) will need to show that message.

    You cannot show a message to an end user from within a trigger.

  3. #3
    Join Date
    Apr 2010
    Posts
    4
    but in my project i have to show it in backend. i donot have any frontent. if i have any frontent then i can do this. now what can i do to solve the problem??

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In the "real world" you would have to do as Shammat says: no user will know that a message was output if it happens only in the backend. However, if this is only an exercise that you will be testing in SQL Plus then you can use DBMS_OUTPUT.PUT_LINE like this:
    Code:
    BEGIN
    
       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;
    
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('The room is not available');
    END cust_room;
    /
    You will need to issue the following SQL Plus command before running the block to enable the messages:

    SET SERVEROUTPUT ON

    And just to reiterate: this is not a viable solution for a real system, because in a real system users do not perform inserts by running SQL Plus!
    Last edited by andrewst; 07-26-10 at 11:25.

  5. #5
    Join Date
    Jul 2010
    Location
    Kim Jong IL's Palace
    Posts
    2
    I Reckon this would do what you are looking for

    BEGIN

    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;
    IF SQL%NOTFOUND THEN
    dbms_output.put_line('The room is not available');
    END IF;
    END cust_room;

Posting Permissions

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