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

If your database is oracle (what seems be according to your code) this could work:

If this is oracle? Wont the trigger mutate when executed?

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.

Well, syntax might slightly differ for other databases. E.g. update statement in trigger definition wouldnt work for MS SQL server.

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

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.

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;

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:Try this and - if it doesn't work properly - say why.