pl/sql- Triggers HELP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: pl/sql- Triggers HELP

  1. #1
    Join Date
    Nov 2005
    Posts
    9

    pl/sql- Triggers HELP

    1. (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

  2. #2
    Join Date
    Dec 2003
    Posts
    4
    What do you want us to do? All that?

  3. #3
    Join Date
    Nov 2005
    Posts
    9
    hi...

    i am trying to do this for my work project..
    i kinda did little...cldnt get thru...

    at present i dont have access to SSH client...

    it will be gr8 if i get help with all or part of it...

  4. #4
    Join Date
    Dec 2003
    Posts
    4
    I doubt anyone on here will do a full project for you, you have to at least try and do it yourself. if you get stuck post up specific problems and we will try and help you.

    onyl way you will learn.

  5. #5
    Join Date
    Nov 2005
    Posts
    9
    i will send the code as soon i get access to shell...
    in mean time can i get feedback on this?

    --------------------------------------------------------------------
    Question:
    (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));
    -----------------------------------------------------------------

    Solution:

    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;

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



    is this right?

  6. #6
    Join Date
    Nov 2005
    Posts
    9
    or how about doing it this way?
    i am little lost..

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

    I think it would be better to "adjust" the TOTAL by the difference between the DELETED and INSERTED recordsets presented to the trigger - allowing for the fact that rows might exist in only one of those tables for an INSERT or DELETE...any comments?

    Something like this ??


    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

  7. #7
    Join Date
    Nov 2005
    Posts
    9
    Compilation Errors:



    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;

  8. #8
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    From the description, it sounds as though they expect a row-level trigger that will adjust the master total for each individual row inserted, updated or deleted. You will therefore need the FOR EACH ROW clause, and you can refer to :new.total and :old.total values and the INSERTING, UPDATING and DELETING functions (e.g. IF DELETING THEN...). For this exercise, I'm assuming we can ignore issues caused by multiple sessions updating the same sales order at the same time.

    Also your UPDATE syntax
    Code:
    UPDATE U
    SET [total] = [total] + T_SUBTOTAL
    FROM dbo.sales_order AS U
    JOIN ...
    is MS SQL, not Oracle. In Oracle there is no FROM or JOIN clause in an UPDATE statement, you must specify a literal name (not an alias) following the UPDATE keyword, and there is no AS for a table alias in the FROM clause. I'm not sure what those square brackets are either. You can however apply an UPDATE to a view or inline view, with certain restrictions.

  9. #9
    Join Date
    Nov 2005
    Posts
    9
    how abt this

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

    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;

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This is obviously an exercise from "academia" -- maintaining parent record totals through a trigger on a child table is a foolish design.

    But, for what it's worth, don't recalculate the entire sum everytime a child record changes. Just adjust the parent record total by the change amount.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width