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

    Unanswered: PLS-00201: identifier 'ORDERLINE_TOTAL' must be declared

    Hi,

    Im new to Oracle and I am having a bit of bother. I think the code below explains it all:

    Code:
    SQL> CREATE OR REPLACE TRIGGER Orderline_price_trg
      2  AFTER UPDATE ON ORDERLINE
      3  BEGIN
      4  Orderline_total := Orderline_quantity * Orderline_price;
      5  END;
      6  /
    
    Warning: Trigger created with compilation errors.
    
    SQL>  show errors trigger Orderline_price_trg;
    Errors for TRIGGER ORDERLINE_PRICE_TRG:
    
    LINE/COL
    --------------------------------------------------------------------------------
    ERROR
    --------------------------------------------------------------------------------
    2/1
    PL/SQL: Statement ignored
    
    2/1
    PLS-00201: identifier 'ORDERLINE_TOTAL' must be declared
    Thanks You

    Dan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >PLS-00201: identifier 'ORDERLINE_TOTAL' must be declared
    so declare ORDERLINE_TOTAL
    what data type is 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
    Apr 2010
    Posts
    4
    Quote Originally Posted by anacedent View Post
    >PLS-00201: identifier 'ORDERLINE_TOTAL' must be declared
    so declare ORDERLINE_TOTAL
    what data type is it?
    it is set to Number:

    Code:
    CREATE TABLE ORDERLINE (
    Orderline_order		Number,	
    Orderline_line		Number,
    Orderline_product	Varchar2(10),	
    Orderline_quantity	Number		CONSTRAINT Orderline_quantity_chk check (Orderline_quantity > 0),	
    Orderline_price		Number		CONSTRAINT Orderline_price_chk check (Orderline_price > 0),
    Orderline_total		Number,
    Orderline_fulfilled	Char(1)		DEFAULT 'N' CONSTRAINT Orderline_fulfilled_chk CHECK (Orderline_fulfilled IN ('Y', 'N')),
    CONSTRAINT Orderline_order_FK 
    	FOREIGN KEY (Orderline_order) 
    	REFERENCES ORDERHEADER(Order_id),
    CONSTRAINT Orderline_line_pk 
    	PRIMARY KEY (Orderline_order, Orderline_line)
    )
    Storage 
    (initial 0K
     	next 132K
    	pctincrease 0);
    Last edited by dankellys; 04-24-10 at 11:04.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it is set to Number
    Only in your mind & not in the trigger itself.

    As soon as you fix 1st error Oracle will complain about Orderline_quantity & Orderline_price which are also not defined.

    TRIGGER is dumb & only know what it is explicitly told about.
    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.

  5. #5
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by anacedent View Post
    >it is set to Number
    Only in your mind & not in the trigger itself.
    .

    Ahh so i have to tell the trigger that ORDERLINE_TOTAL is a Number? Do you define that in the SQL statement part of the trigger?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails Read The Fine Manual

    Overview of PL/SQL

    Ask Tom Home
    site above contains many fine coding examples
    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.

  7. #7
    Join Date
    Apr 2010
    Posts
    4
    Sorted, thanks alot for your help

    Code:
    CREATE OR REPLACE TRIGGER Orderline_price_trg
    AFTER UPDATE ON ORDERLINE
    DECLARE
      	Orderline_total   NUMBER;
    	Orderline_quantity NUMBER;
    	Orderline_price NUMBER;
    BEGIN
    Orderline_total := Orderline_quantity * Orderline_price;
    END;
    /

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While trigger is valid & does not throw any error, it accomplishes NOTHING;
    The database contents remain unchanged.

    By The Way, it is considered suboptimal to store computed values in the DB.
    All computed values can be calculated at data presentation time should that be necessary.
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    I guess you want to change the column value, in that case you need to reference the "NEW" colum
    Code:
    CREATE OR REPLACE TRIGGER Orderline_price_trg
    BEFORE UPDATE ON ORDERLINE
    BEGIN
       :NEW.Orderline_total := :NEW.Orderline_quantity * NEW.Orderline_price;
    END;
    And you want a BEFORE UPDATE trigger, otherwise the new value will not be stored anyway. After update is triggered after the data has been written, just as the name says.

    But I agree with anacedent that it's probably not a very good idea to store computed values, especially when the computation is that simple.

Posting Permissions

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