Results 1 to 5 of 5

Thread: Trigger Help

  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: Trigger Help

    I've got some good advice her the last few days so have one question on triggers. I need to create a trigger that will use data in one table to update the data in another table. Never done a trigger this way before

    Here are my two tables

    Code:
    
    create table orderheader
    (
    Order_id number(4) not null constraint order_id check(Order_id >0) constraint orderid_pk primary key,
    Order_date date not null,
    Order_customer varchar2(10) not null constraint ordercustomer_fk references customer(Cust_id),
    Order_delivery number(4) not null constraint order_delivery check(Order_delivery >0),
    Order_total number(4) constraint order_total check(Order_total >0),
    Order_vat number(4),
    Order_payment number(4),
    Order_completed date,
    Order_credit char(1) constraint order_credit check(Order_credit in('Y' , 'N')),
    Order_takenby number(4) constraint order_takenby check(Order_takenby >0) constraint ordertakenby_fk references employee(Emp_id)
    )
    storage
    ( initial 1m
    next 1m
    pctincrease 0);
    Code:
    
    create table orderline
    (
    Order_order number(4) constraint order_fk references orderheader(Order_id),
    Orderline_line number(4),
    Orderline_product varchar2(10)  constraint orderline_fk references product(Product_id),
    Orderline_quantity number(4) constraint orderline_quantity check(Orderline_quantity >0),
    Orderline_price number(4) constraint orderline_price check(Orderline_price >0),
    
    Orderline_total number(4),
    constraint orderline_total check(Orderline_total = Orderline_price*Orderline_quantity),
    Orderline_fulfilled char(1) default 'N' constraint orderline_fulfilled check (Orderline_fulfilled in ('Y', 'N')),
    constraint comprimarykey_orderline primary key(Orderline_line, Order_order)
    )
    
    storage
    (initial 16m
    next 16m
    pctincrease 0);
    What I need to do is create a trigger that when the orderline_fulfilled is set to 'Y' this will update the order_completed in orderheader to sysdate:

    Here is my attempt;

    Code:
    create or replace trigger orderscomplete
    before insert on orderheader
    for each row
    DELCARE(orderline.order_fufilled ='Y')
    BEGIN
    :NEW,order_complete :=SYSDATE
    END;
    /
    I know this is far off just never done triggers that reference other tables, thus far.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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 2011
    Posts
    23
    You've lost me with that last post

  4. #4
    Join Date
    Apr 2011
    Posts
    23
    Code:
    create or replace trigger orderscomplete
    before update of order_completed on orderheader
    for each row
    when(orderline.orderline_fufilled = 'Y')
    begin
    :new.order_completed :SYSDATE;
    end;
    /
    This is my updated column after reading online but it still wont ork

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is my updated column after reading online but it still wont ork
    REALLY?

    You need to fix the syntax errors.
    Code:
      1  create or replace trigger orderscomplete
      2  before update of order_completed on orderheader
      3  for each row
      4  when(orderline.orderline_fufilled = 'Y')
      5  begin
      6  :new.order_completed :SYSDATE;
      7* end;
    SQL> /
    when(orderline.orderline_fufilled = 'Y')
         *
    ERROR at line 4:
    ORA-04076: invalid NEW or OLD specification
    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.

Posting Permissions

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