Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: Triggers-- Help!

    (a) Create a trigger on table LINE_ITEM which maintains the correct value of TOTAL in SALES_ORDER. That is, for a particular order_id, TOTAL in SALES_ORDER table is a summation of all TOTAL values in LINE_ITEM for that order_id

    - Updates the total in SALES_ORDER table when a record is inserted into LINE_ITEM .
    - Updates the total in SALES_ORDER when a record is deleted from LINE_ITEM table.
    - Updates the total in SALES_ORDER table when total for a order in LINE_ITEM is changed i.e updated.

    (a) Delete First line item of order_id 606.
    (b) Display the contents of SALES_ORDER table of order 606
    (c) Insert into line_item values (606,1,100860,35,10,350);
    (d) Display the contents of SALES_ORDER table of order 606


    --------------------------------------------------------------------



    DROP TABLE sales_order CASCADE CONSTRAINTS;
    CREATE TABLE sales_order (
    order_id NUMBER(4) NOT NULL,
    order_date DATE,
    customer_id NUMBER(6),
    ship_date DATE,
    total NUMBER(8,2));

    drop table LINE_ITEM CASCADE CONSTRAINTS;
    create table LINE_ITEM (
    ORDER_ID NUMBER(4) NOT NULL,
    ITEM_SEQ NUMBER(4) NOT NULL,
    PRODUCT_ID NUMBER(6),
    ACTUAL_PRICE NUMBER(8,2),
    QUANTITY NUMBER(8),
    SUBTOTAL NUMBER(8,2));

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    If your database is oracle (what seems be according to your code) this could work:
    Code:
    CREATE TRIGGER total_Sales_trg AFTER UPDATE OR DELETE OR INSERT ON LINE_ITEM
    BEGIN
    	update SALES_ORDER s
    	SET total = (select sum(subtotal) from LINE_ITEM i where i.order_id = s.order_id);
    END total_Sales_trg;

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    If this is oracle? Wont the trigger mutate when executed?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, as Madafaka's example shows a table level trigger; if it was statement level trigger, then it would produce mutating table error.

    By the way, table is mutating, not the trigger.

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Well, syntax might slightly differ for other databases. E.g. update statement in trigger definition wouldnt work for MS SQL server.

  6. #6
    Join Date
    Nov 2005
    Posts
    3
    i m trying to work on this and see if it compiles...

    i didnt quiet understand this Question-- can someone help?

    -------------------------------------------------------------------

    (Trigger) LogEmpChange
    (a) Create a table called EmpAudit that keeps track of all the changes made to Salary, SuperSSN, and DNO of EMPLOYEE table as follows: (4)
    EmpAudit ( AuditSeqID NUMBER(5) PRIMARY KEY,
    Emp_ID NUMBER(4) NOT NULL,
    Change_Type CHAR(1) NOT NULL, -- one of I/U/D
    Changed_By VARCHAR2(10),
    Time_Stamp DATE NOT NULL,
    Old_Salary NUMBER(10),
    Old_Mgr_ID NUMBER(4)
    Old_job_code NUMBER(2)
    New_Salary NUMBER(10),
    New_MgrID NUMBER(4),
    New_job_code NUMBER(2) );

    Emp_ID represents the person whose data is changed.
    Create a unique sequence for AuditSeqID beginning from 00001 (use CREATE SEQUENCE statement). Increase the value of AuditSeqID by 1 automatically for every insertion.
    Changed_By will store the user name who updated the table. Use the pseudo column name called USER to determine the value to store in Changed_by. USER is a pseudo column pre-defined in Oracle.
    For Change_Type, use "I" for Insert, "D" for Delete, and "U" for Update commands. Define the valid Change_Type using CHECK clause in CREATE command.
    Time_Stamp attribute must enter/display date of the change.

    (b) Create a trigger called LogEmpChange (with proper naming convention discussed in the lecture note) that writes every meaningful change against EMPLOYEE table into EmpAudit table as shown above. Note that for update case, you have to add a record to EmpAudit only when the audited attribute is changed. For deletion case, you have to add a record with the deleted value of those audited attributes. (10)

    (c) Do the following sequence of updates for testing your trigger. (3)
    - Insert John Doe with emp_ID = 9999 and manager_ID = 7508 and other arbitrary data
    - Increase the salary of LYNN DENNIS into $5000.
    - Change the commission of JEAN KELLY into 10% of her salary
    - Change the job code of CYNTHIA WARD into 40.
    - Delete (with cascade option) John Doe

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There are a few good men here who could do it in no time, but I guess that's not the point. Doing your homework won't help you in long term.

    Therefore, perhaps just a suggestion: post the code you already wrote; what errors did you receive when you executed this code? Ask for a specific advice, because "i didnt quiet understand this Question" seems to be too general. What exactly did you not understand?

    Finally - pay more attention during the class.

  8. #8
    Join Date
    Nov 2005
    Posts
    3

    Compilation Errors- Triggers


    Create a trigger on table LINE_ITEM which maintains the correct value of TOTAL in SALES_ORDER. That is, for a particular order_id, TOTAL in SALES_ORDER table is a summation of all TOTAL values in LINE_ITEM for that order_id

    - Updates the total in SALES_ORDER table when a record is inserted into LINE_ITEM .
    - Updates the total in SALES_ORDER when a record is deleted from LINE_ITEM table.
    - Updates the total in SALES_ORDER table when total for a order in LINE_ITEM is changed i.e updated.

    (a) Delete First line item of order_id 606.
    (b) Display the contents of SALES_ORDER table of order 606
    (c) Insert into line_item values (606,1,100860,35,10,350);
    (d) Display the contents of SALES_ORDER table of order 606[/B]






    CODE:


    CREATE TRIGGER total_Sales_trg AFTER UPDATE OR DELETE OR INSERT ON LINE_ITEM
    BEGIN
    UPDATE U
    SET [total] = [total] + T_SUBTOTAL -- Adjust by the "difference"
    FROM dbo.sales_order AS U
    JOIN
    (
    SELECT [T_ORDER_ID] = ORDER_ID,
    [T_SUBTOTAL] = + SUM(COALESCE(I.SUBTOTAL), 0)
    - SUM(COALESCE(D.SUBTOTAL), 0)
    FROM inserted I
    FULL OUTER JOIN deleted D
    ON D.ORDER_ID = I.ORDER_ID
    AND D.ORDER_ID = I.ORDER_ID
    GROUP BY ORDER_ID
    ) T
    ON T_ORDER_ID = U.ORDER_ID

    END total_Sales_trg;

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is far too complicated query and, in my opinion, is not worth debugging ... requested task can be done much more easy.

    I *think* that you're still a novice and - if I'm not wrong - exercises you'll receive these days *should* be simple. Therefore, if you end up with a code that is impossible to read and understand for your mother (check my example code - it can be read as a pure English story), you'd probably try the other way.

    This is just an example:
    Code:
    CREATE OR REPLACE TRIGGER total_sales_trg
       AFTER UPDATE OR DELETE OR INSERT
       ON line_item
    BEGIN
       UPDATE sales_order s
          SET s.total = (SELECT SUM (l.total)
                           FROM line_item l
                          WHERE l.order_id = s.order_id);
    END;
    Try this and - if it doesn't work properly - say why.

Posting Permissions

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