TRIGGERS! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

TRIGGERS!

Author

Topic

mysticmoonlight
Starting Member

1 Post

Posted - 2005-11-26 : 09:34:58

Hi,

I am suppose to 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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-26 : 09:50:49

Is this MS Sql Server? NUMBER(4) is not a valid datatype.

>>is this right?
did you try to compile your trigger? If it compiled, did you try your a-d scenarios and check results?

issues:
For deletes, inserted table won't contain anything.
For inserts of new orders, will Sales_Order contain the new order_id?
Your update statement in the trigger isn't formed properly:

update s set total = (select...)
from Sales_Order s
join inserted i on i.order_id = s.order_id

Give it another shot and post back with a specific question if you're still having problems

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 12:53:40

update SALES_ORDER s
SET total = (select sum(subtotal) from LINE_ITEM i where i.order_id = s.order_id)

I wouldn't have done it this way because the server will have to process every LINE_ITEM whenever one [or more] of them changes, and if two LINE_ITEMs change in a single "batch" then the process will be called multiple times to make the same update, both of which are pretty wasteful of CPU cycles.

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.

Something like this - hopefully! :


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

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources