Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: updation in one table should reflect to another table.

    Hi guys,
    i ahve two tables - Order_details and Order_history.
    Order_details - keeps the record of the orders given by the customer and
    Order_history - heeps the history of the Order_details for factory use.

    whatever data i have in Order_details, i will be having same data in Order_history but the probem is that suppose customer changes his requirment then updation in the table Order_details should reflect in the table Order_history.

    Structure of Order_details:-

    Sl_no(P.k) product_id(f.k) cust_code(f.k) quantity shipping_date
    ---------- ------------ ------------- ------- -------------
    101 pr-abc a-1101 50 10/10/2011
    102 pr-5th b-2301 40 11/11/2011
    103 pr-6yh c-8790 30 12/12/2011


    Sl_no is a parimary key and Sl_no, product_id, cust_code are common in both the tables as another tables has a same structureSuppose if we update anything from this table mainly quantity from 50 to 45 for customer "a-1101" or shipping date from 10th to 12th , then changes should reflect in another table.

    will trigger do? If eys then how to match up teh condition?[]

    Thanks

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Base your trigger's code on PK:
    1. INSERT on Order_details --> INSERT INTO Order_history VALUES ... your values ...
    2. UPDATE on Order_details --> UPDATE Order_history SET ... some fields ... WHERE Sl_no = Order_details.Sl_no
    3. DELETE on Order_details --> DELETE FROM Order_history WHERE Sl_no = Order_details.Sl_no
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile tHanks guys for going through my probelm. I did it finally :)

    solution is:-
    -----------


    create TRIGGER OrderRevisionHistory ON Order_details
    AFTER update
    AS
    UPDATE
    Order_history
    SET
    quantity = (select quantity from Order_details
    WHERE
    Order_history.sl_no = Order_details.sl_no)

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You need to read this: Multirow Considerations for DML Triggers, as your solution might get you into troubles.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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