pl/sql- Triggers HELP

[mysticmoonlight]

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

[decoy]

What do you want us to do? All that?

[mysticmoonlight]

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

[decoy]

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.

[mysticmoonlight]

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?

[mysticmoonlight]

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

[mysticmoonlight]

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;

[WilliamR]

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

[mysticmoonlight]

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;

[slimdave]

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.