Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: Simple trigger.. not to me

    Hey guys,

    The picture:

    Basically I have a valuation table and customer table. The customer has sub tables buyer and seller. This means a customer is either a buyer or seller. The valuation table has a customer_ref which is linked to customer table and then to either the buyer or seller table.

    I need a trigger that when a column (valuation date) in the valuation tabel is updated it will check to see if the customer (which is raelated to the valuation date) is either a buyer or seller. If its a buyer then an error msg will appear. This is my code but it doesnt work.. please help!

    drop trigger buyer_check;

    create trigger buyer_check
    before update of Valuation_Date
    on valuation
    for each row
    declare
    dummy integer;
    begin
    select count(*) into dummy
    from buyer
    where buyer_Cust_Ref = Customer_Ref_No;

    if (dummy > 0)
    then raise_application_error(-20601,'ITS A BUYER');
    end if;
    end;

    /

    Jim

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Simple trigger.. not to me

    How about

    Code:
    drop trigger buyer_check;
    
    create trigger buyer_check
    before update of Valuation_Date
    on valuation
    for each row
    declare
    	dummy  integer;
    begin
    	select count(*) into dummy
    	from buyer
    	where buyer_Cust_Ref = :NEW.Customer_Ref_No;
    
    	if (dummy > 0)  
    	then 	raise_application_error(-20601,'ITS A BUYER');
    	end if;
    end;

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    Hi,

    I still doesnt work, says trigger created but with complication errors.

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Show Errors

  5. #5
    Join Date
    Mar 2004
    Posts
    12
    ah thanx i didnt know about that little trick, any how these were the errors:

    4/2 PL/SQL: SQL Statement ignored
    6/25 PL/SQL: ORA-00904: "CUSTOMER_REF_NO": invalid identifier

    this is the customer ref in the valuation table. So how do i reference that to those in the buyer table?

    Basically i want the trigger to checks if the cust_ref_no in valuation table matches any of those in the buyer table, then produce an error msg.

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    What are your table descriptions?

    SQL> Desc Valuation;

    SQL> Desc Buyer;

  7. #7
    Join Date
    Mar 2004
    Posts
    12
    As follows:

    SQL> desc buyer
    Name Null? Type
    ----------------------------------------- -------- ------------
    BUYER_CUST_REF# NOT NULL NUMBER(5)
    PROPERTY_TYPE_REQUIRED VARCHAR2(4)

    SQL> desc valuation
    Name Null? Type
    ----------------------------------------- -------- ------------
    APPOINTMENT_CODE NOT NULL VARCHAR2(5)
    PROPERTY_CODE VARCHAR2(6)
    CUSTOMER_REF_NO NUMBER(5)
    VALUER_NAME VARCHAR2(20)
    VALUATION_DAY VARCHAR2(9)
    VALUATION_DATE NOT NULL DATE
    VALUATION_TIME VARCHAR2(15)

  8. #8
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Looks like you have a # on the end of Buyer_Cust_Ref. So change the code to use the correct column name and try again.

  9. #9
    Join Date
    Mar 2004
    Posts
    12
    it still doesnt work, i get this error:

    LINE/COL ERROR
    -------- ---------------------------------------------------------------
    4/2 PL/SQL: SQL Statement ignored
    6/26 PL/SQL: ORA-00904: "CUSTOMER_REF_NO": invalid identifier

    So annoying! It needs to know what customer_ref_no is. I need to declare it as being on the valuation table some how.

    jim

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Are you using NEW infront of the column name?

    where buyer_Cust_Ref# = :NEW.Customer_Ref_No;

    Works for me. Here is what I have.

    Code:
    create table buyer ( 
    BUYER_CUST_REF# NUMBER(5) NOT NULL,
    PROPERTY_TYPE_REQUIRED VARCHAR2(4));
    
    create table valuation (
    APPOINTMENT_CODE VARCHAR2(5) NOT NULL,
    PROPERTY_CODE VARCHAR2(6),
    CUSTOMER_REF_NO NUMBER(5),
    VALUER_NAME VARCHAR2(20),
    VALUATION_DAY VARCHAR2(9),
    VALUATION_DATE DATE NOT NULL,
    VALUATION_TIME VARCHAR2(15));
    
    create or replace trigger buyer_check
    before update of Valuation_Date
    on valuation
    for each row
    declare
    	dummy  integer;
    begin
    	select count(*) into dummy
    	from buyer
    	where buyer_Cust_Ref# = :NEW.Customer_Ref_No;
    
    	if (dummy > 0)  
    	then 	raise_application_error(-20601,'ITS A BUYER');
    	end if;
    end;
    
    insert into valuation (APPOINTMENT_CODE,CUSTOMER_REF_NO,VALUATION_DATE)
    values ('XYZ','1001',sysdate);
    
    insert into buyer (BUYER_CUST_REF#)
    values ('1001');
    
    commit;
    when I run

    update valuation
    set VALUATION_DATE = sysdate+1;

    I get the Ora 20601 error.

  11. #11
    Join Date
    Mar 2004
    Posts
    12
    Wow its works thank you so much for your help but....

    Theres always a but.. sry

    The application error does show when i enter a date into valuation with a customer being in the buyer table.


  12. #12
    Join Date
    Mar 2004
    Posts
    12
    Ah no its me being silly, i was trying to insert data, i havent put that in the statement clause!

    So it does work!!!

    Wow thank you so much, i appriciate you taking time to help me!

    Yey! im a very happy man!!!

    Thank you!!!

    Regards

    James

Posting Permissions

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