Results 1 to 8 of 8

Thread: Trigger Help!!

  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Trigger Help!!

    Hello, so I need to create a trigger that will update the orderheader table and set order_completed to sysdate when all the orderlines have been fulfilled

    create or replace trigger Orders_complete
    before update of Order_completed on OrderHeader
    for each row
    WHEN
    (orderline.order_fufilled ='Y')
    BEGIN
    INSERT INTO OrderHeader VALUES (Order_completed)
    (:OLD.Order_completed,:NEW.Order_completed=SYSDATE )
    END;
    /

    but it just says that the trigger has been created with compilation errors!!

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT statement is invalid syntax
    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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Also I've never seen an orphan "WHEN" in the trigger declaration.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2012
    Posts
    4
    changed to when as it said the declare statement was not correct?!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle/PLSQL: BEFORE UPDATE Trigger

    Code:
    CREATE OR REPLACE TRIGGER orders_before_update
    BEFORE UPDATE
        ON orders
        FOR EACH ROW
    
    DECLARE
        v_username varchar2(10);
    
    BEGIN
    
        -- Find username of person performing UPDATE on the table
        SELECT user INTO v_username
        FROM dual;
    
        -- Update updated_date field to current system date
        :new.updated_date := sysdate;
    
        -- Update updated_by field to the username of the person performing the UPDATE
        :new.updated_by := v_username;
    
    END;
    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.

  6. #6
    Join Date
    Apr 2012
    Posts
    4
    well I think this is ok... although it still says trigger completed with compilation errors

    create or replace trigger Orders_complete
    before update of Order_completed on OrderHeader
    for each row
    BEGIN
    INTO OrderHeader VALUES (Order_completed)
    (:NEW.Order_completed :=SYSDATE)
    END;
    /

    Orderline.Orderline_fulfilled = 'Y' needs to come into it somewhere though and I'm not sure where it belongs

    thanks

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You have no idea what SQL (not to mention PL/SQL!) is, have you? Did you ever take time to read about basic syntax? According to your unsuccessful attempts, I don't think you did.

    But hey, it is a weekend ahead, go to Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation and read some documentation! By the middle of the next week, you'll be an advanced beginner!

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    removed. I shouldn't do homework.
    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
  •