Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Creating Trigger To Update Total On 'Parent' Table Expand / Collapse
Author
Message
Posted Thursday, September 20, 2012 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 6:26 AM
Points: 9, Visits: 39
Hi,
I have two Table, Order and OrderDetail

Create Table [Order]
(
OrderID BigInt Identity(1,1) Primary Key,
OrderTotal Decimal(22,2) Not Null
)

Create Table [OrderDetail]
(
OrderDetailID BigInt Identity(1,1) Primary Key,
Total Decimal(22,2) Not Null,
OrderID BigInt Not Null,
Constraint [FK_OrderDetail-OrderID] Foreign Key [OrderID] References [Order](Id)
ON DELETE CASCADE
)

What I want is very simple, I want to create trigger that every inserting, updating, deleting OrderDetail row,
Order.OrderTotal always sync with SUM of OrderDetail.OrderDetailTotal value.

I'm Firebird user, in Firebird context value (inserted, updated, or deleted) in trigger is always single row which not always true in Sql Server.
Post #1361904
Posted Thursday, September 20, 2012 7:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
I'm Firebird user, in Firebird context value (inserted, updated, or deleted) in trigger is always single row which not always true in Sql Server.

To make it clearer for you, SQL Server uses two virtual tables in a trigger Inserted and Deleted. Both have all the values for the rows affected during the event that activated the trigger.
You shoulduse this tables (Inserted in this case) to create your trigger and make it work for multiple rows.
What you're asking is easy if you understand this, that's why I prefer to explain it rather than giving you some code.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1361938
Posted Thursday, September 20, 2012 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
F8R (9/20/2012)
Hi,
I have two Table, Order and OrderDetail

Create Table [Order]
(
Id BigInt Identity(1,1) Primary Key,
OrderTotal Decimal(22,2) Not Null
)

Create Table [OrderDetail]
(
Id BigInt Identity(1,1) Primary Key,
OrderDetailTotal Decimal(22,2) Not Null,
OrderID BigInt Not Null,
Constraint [FK_OrderDetail-OrderID] Foreign Key [OrderID] References [Order](Id)
ON DELETE CASCADE
)

What I want is very simple, I want to create trigger that every inserting, updating, deleting OrderDetail row,
Order.OrderTotal always sync with SUM of OrderDetail.OrderDetailTotal value.

I'm Firebird user, in Firebird context value (inserted, updated, or deleted) in trigger is always single row which not always true in Sql Server.


What you are describing is the very reason you should NOT store calculated data like this. If you want the order total you should get it by the sum of the OrderDetails. You are asking for problems storing your total like this. It is one thing to store your total for historical purposes when an order is completed but it sounds like you are changing this data on a routine basis.

You really should not use a generic ID as a column name. Your column should give you some idea of what it is. OrderID and OrderDetailID would be much better. Column names should remain constant throughout the system. OrderID should ALWAYS be OrderID instead of ID in one table and OrderID in another table. You should also try to avoid reserved words for object names (Order).

I took a shot at changing up your ddl to show you what I mean.

Create Table [Orders]
(
OrderId BigInt Identity(1,1) Primary Key
)

go

Create Table [OrderDetails]
(
OrderDetailsId BigInt Identity(1,1) Primary Key,
OrderDetailTotal Decimal(22,2) Not Null,
OrderID BigInt Not Null,
Constraint [FK_OrderDetail-OrderID] Foreign Key (OrderID) References [Orders](OrderId)
ON DELETE CASCADE
)

go

create view OrderSummary
as
select o.OrderID, SUM(OrderDetailTotal) as OrderTotal
from Orders o
join OrderDetails od on od.OrderID = o.OrderID
group by o.OrderID

go

select * from OrderSummary

The main advantage is that there is no need to update your order table when items are added or removed from the order. It just gets the total when you need it. It will always be up to date.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1362028
Posted Thursday, September 20, 2012 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 6:26 AM
Points: 9, Visits: 39
Thank you for all hint and sugestion, I think I need to unlearn my 'old' knowledge, :D.

I'm new to Sql Server and honestly, I'm not very good at Database Design because my main job right now is
.NET Developer.
Post #1362240
Posted Thursday, September 20, 2012 12:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
F8R (9/20/2012)
Thank you for all hint and sugestion, I think I need to unlearn my 'old' knowledge, :D.

I'm new to Sql Server and honestly, I'm not very good at Database Design because my main job right now is
.NET Developer.


This is a great place to learn some new ideas about how to design your structures. That is not to say that everything you read is always the best way to do something but there are a lot of people around with a lot of experience doing this type of stuff. As with everything you should always evaluate the suggestions and see if they actually make sense. It is sometimes easier to see those kinds of details when it is a project you are not as closely tied to.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1362244
Posted Thursday, September 20, 2012 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 6:26 AM
Points: 9, Visits: 39
Sean Lange (9/20/2012)
F8R (9/20/2012)
Thank you for all hint and sugestion, I think I need to unlearn my 'old' knowledge, :D.

I'm new to Sql Server and honestly, I'm not very good at Database Design because my main job right now is
.NET Developer.


This is a great place to learn some new ideas about how to design your structures. That is not to say that everything you read is always the best way to do something but there are a lot of people around with a lot of experience doing this type of stuff. As with everything you should always evaluate the suggestions and see if they actually make sense. It is sometimes easier to see those kinds of details when it is a project you are not as closely tied to.



This site and forum is very informative, I'm already readed 'Stairways' series, do you have any recomendation
on good Sql Server Database Design book ?

I think, I need to have good knowledge on Database Design first before I learn other area.
Post #1362252
Posted Friday, September 21, 2012 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 6:26 AM
Points: 9, Visits: 39
I finally decided to use CTE in this trigger after reading Sql Documentation and googling:

CREATE TRIGGER [trUpdateOrderTotal]
ON [dbo].[OrderDetail]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
Declare @Evt Int = 0;
SET NOCOUNT ON

IF(EXISTS(SELECT * FROM inserted))
Set @Evt = @Evt + 1;
IF(EXISTS(SELECT * FROM deleted))
Set @Evt = @Evt + 2;

IF(@Evt = 1) -- inserted
begin
With SubTotal(OrderID, SubTotal) As
(
SELECT OD.OrderID, SUM(COALESCE(OD.Total, 0)) As SubTotal FROM OrderDetail OD
WHERE OD.OrderID IN (SELECT Distinct(OrderID) From inserted)
GROUP BY OD.OrderID
)
UPDATE O SET O.OrderTotal = S.SubTotal
FROM [Order] O JOIN SubTotal S ON S.OrderID = O.OrderID
end

if(@Evt = 2) -- deleted
begin
With SubTotal(OrderID, SubTotal) As
(
SELECT OD.OrderID, SUM(COALESCE(OD.Total, 0)) As SubTotal FROM OrderDetail OD
WHERE OD.OrderID IN (SELECT Distinct(OrderID) From deleted)
GROUP BY OD.OrderID
)
UPDATE O SET O.OrderTotal = S.SubTotal
FROM [Order] O JOIN SubTotal S ON S.OrderID = O.OrderID
end

if(@Evt = 3) -- updated
begin
With SubTotal(OrderID, SubTotal) As
(
SELECT OD.OrderID, SUM(COALESCE(OD.Total, 0)) As SubTotal FROM OrderDetail OD
WHERE OD.OrderID IN (SELECT Distinct(OrderID) From inserted WHERE COLUMNS_UPDATED(Total) = 1)
GROUP BY OD.OrderID
)
UPDATE O SET O.OrderTotal = S.SubTotal
FROM [Order] O JOIN SubTotal S ON S.OrderID = O.OrderID
end
END

Post #1362727
Posted Friday, September 21, 2012 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
I still say you are asking for nothing but trouble trying to keep this stuff stored like this but if you are insistent on doing it then I would make a couple recommendations.

I would not use that @Evt as control for your code. It is clever but rather confusing.

if EXISTS(select * from inserted) and NOT EXISTS(select * from deleted)
--do your inserted stuff here

if EXISTS(select * from deleted) and NOT EXISTS(select * from inserted)
--do your deleted stuff here

if EXISTS(select * from deleted) and EXISTS(select * from inserted)
--do your update stuff here


Better still (again assuming you can't just calculate your total on the fly) would be use the MERGE statement. http://msdn.microsoft.com/en-us/library/bb510625.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1362749
Posted Friday, September 21, 2012 10:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
You have several fundamental errors.

1. Tables model sets so their names are plural or (better) collective nouns. See ISO-11179 or any book on data modelign.
2. IDENTITY is a non-relational physical numbering and cannot be used a primary key in RDBMS.
3. The choice of data types is important; order numbers should be CHAR(n) with a check digit or regular expression for validation; do you really expect to get more order than the number of atoms in the universe?
4. What you are trying to do is called a “non-normal form redundancy”; read Tom Johnston. http://www.information-management.com/infodirect/20010914/4007-1.html
5. Order_Details are a weak entity (they exists only if an order exists), so their key must include the order_nbr
6. The order details should have the attributes of the things that make the order. These items will usually a SKU (stock keeping unit) or UPC code to identify them.

Here is the usual idiom for this kind of strong-weak entity model:

CREATE TABLE Orders
(order_nbr CHAR(15) NOT NULL PRIMARY KEY
CHECK (order_nbr LIKE '[0-9]...'),
<< attributes of the order qua order >> );

CREATE TABLE Order_Details
(order_nbr CHAR(15) NOT NULL,
REFERENCES Orders (order_nbr)
ON DELETE CASCADE,
sku CHAR(15) NOT NULL – gtin code
REFERENCES Inventory(sku),
PRIMARY KEY (order_nbr, sku),
unit_price DECIMAL (12,2) NOT NULL
CHECK (unit_price >= 0.00)
order_qty INTEGER NOT NULL
CHECK (order_qty > 0));

To get what you want, we use a VIEW that is always correct, does not need constant updating by a trigger full of procedural code (ugh!).

CREATE VIEW Order_Totals (order_nbr, order_amt_tot)
AS
SELECT order_nbr, SUM(D.unit_price * D.order_qty)
FROM Orders AS O, Order_Details AS D
WHERE O.order_nbr = D.order_nbr
GROUP BY O.order_nbr;




Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1362824
Posted Friday, September 21, 2012 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
Joe's reply is somewhat echoing what I have stated but he knows the actual terms for these things.

I would not however recommend that you use his primary key idea for your OrderDetails table. (PRIMARY KEY (order_nbr, sku)). In theory this sounds like it will work but I can tell you from experience that in the long run it will not work. Once you start allowing for discounts it get very complicated to work with this.

Here is a very realistic example. You have a SKU (1234) that sells for $9.99 but you run a special for buy 3 get 1 free. How do you store this? You can only have 1 row in your table per order. Quantity is 4 but what is the price? You need the total to be $29.97. Do you store the price as 7.49? Now your total is only 29.96. The best way to store OrderDetails is NOT to get locked in like that. You should have 2 rows in your table for this SKU, the first row with a quantity of 3 and price of 9.99 and a second row with a quantity of 1 and price of 0. With Joe's model you can't store accurate data about the transaction.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1362840
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse