Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Trigger witch check on different table

    Hi Guys,

    New to Oracle and wanted to create a simple trigger..This should be fairly simple..been trying to get this done all morning and tried to search for answers but couldn't find any that would work.

    I am basically trying to create a trigger that prevents new records to be created on one table by checking on a different table for a condition.

    Basically if there is no records on the waiter table with Y for active dont let new bills being inserted on the bill table.

    Create or Replace Trigger TR_CHECK_ACTIVE_WAITER
    Before Insert
    On Bill
    For Each Row
    Begin
    IF WAITER.ACTIVE_YN='N' Then
    raise_application_error(-20901, 'No active waiter');
    End If;
    End;


    With this i am getting

    Error(2,11): PLS-00357: Table,View Or Sequence reference 'WAITER.ACTIVE_YN' not allowed in this context

    What am i missing? Thanks in advance.

    Peter

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    Create or Replace Trigger TR_CHECK_ACTIVE_WAITER
     Before Insert  
     On Bill
    REFERENCING NEW AS NEW OLD AS OLD
    For Each Row
    declare
    cnt number;
    begin 
    select count(*)
    into cnt
    from waiter
    where waiter_id = :new.waiter_id
    and active_YN = 'Y';
    
    if cnt = 0 then
      raise_application_error(-20901, 'No active waiter');
    end if;
    End;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is 'WAITER.ACTIVE_YN' ?
    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.

  4. #4
    Join Date
    Oct 2010
    Posts
    3
    Hi Sorry about that.

    Waiter is a table, Active_YN is field of Char(1) type on that table. Basically if all waiters are inactive (Active_YN='N') i should not be able to enter bills on the BILL table.. that's the goal of the trigger...

    Thanks for your help.

    P

  5. #5
    Join Date
    Oct 2010
    Posts
    3
    Quote Originally Posted by beilstwh View Post
    Code:
    Create or Replace Trigger TR_CHECK_ACTIVE_WAITER
     Before Insert  
     On Bill
    REFERENCING NEW AS NEW OLD AS OLD
    For Each Row
    declare
    cnt number;
    begin 
    select count(*)
    into cnt
    from waiter
    where waiter_id = :new.waiter_id
    and active_YN = 'Y';
    
    if cnt = 0 then
      raise_application_error(-20901, 'No active waiter');
    end if;
    End;
    Thanks for your help Bill, that works. I'm wondering if instead of the select and to a counter we could use a cursor and select into it the active_yn and do the if with it.

    Thanks!
    P

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Yes, that would work but would be no faster.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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