September 20, 2012 at 6:19 am
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.
September 20, 2012 at 7:03 am
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.
September 20, 2012 at 8:32 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 12:44 pm
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.
September 20, 2012 at 12:49 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 1:01 pm
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.
September 21, 2012 at 8:45 am
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))
IF(EXISTS(SELECT * FROM deleted))
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
September 21, 2012 at 8:59 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 10:36 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 10:42 am
CELKO (9/21/2012)
You have several fundamental errors.
Let's begin:
1. Tables model sets so their names are plural or (better) collective nouns. See ISO-11179 or any book on data modelign.
Agree, that's a valid statement.
2. IDENTITY is a non-relational physical numbering and cannot be used a primary key in RDBMS.
It can and it is used very frequently by well designed SQL Server databases. I don't wish to start a new debate on this.
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?
This one made me laugh. Your char(15) actually could hold more values than a bigint and it uses almost twice the space. It's also much more difficult to work with in a large OLTP system. What you're trying to do is to replicate the order numbers used on printed orders.
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
That's good information that will be helpful for many. I edited it to make it easier for everyone to read it.
5. Order_Details are a weak entity (they exists only if an order exists), so their key must include the order_nbr
That was already there.
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.
I'm sure his real table will have much more columns than the ones showing here.
Your suggestion is valid, but it was already made by Sean.
I hope F8R won't stay just with the knowledge in your books and realizes that there are different theories that as valid as yours and all have applicable scenarios.
September 21, 2012 at 4:13 pm
Sean Lange (9/21/2012)
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
Thanks again for your advice, I will take a look at MERGE statement, I might not use this code in a production database, but I want to know how to doing that using trigger in Sql Server.
So this is part of my learning Sql Server, :D.
CELKO (9/21/2012)
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;
Thanks, replying at your advice 5-6, original Table schema already have that column but I excluded it in my original post for simplicity.
September 22, 2012 at 3:49 am
Ok, I'm hijacking my own thread, 😀
Create Table Orders
(
OrderID BigInt Identity(1,1) Not Null,
CustomerID BigInt Not Null,
Constraint [PK_Orders] Primary Key(OrderID)
)
go
Create Table Customers
(
CustomerID BigInt Not Null Identity(1,1),
-- CustomerType Int Not Null Default(0),
Constraint [PK_Customers] Primary Key(CustomerID)
)
go
At this design, Orders only accept registered Customer, but I want Non Registered Customer can place Order, so I add CustomerType flag column (for Customer = 0, Non Customer = 1), is this approach practical ?
September 22, 2012 at 10:32 am
F8R (9/22/2012)
Ok, I'm hijacking my own thread, 😀
Create Table Orders
(
OrderID BigInt Identity(1,1) Not Null,
CustomerID BigInt Not Null,
Constraint [PK_Orders] Primary Key(OrderID)
)
go
Create Table Customers
(
CustomerID BigInt Not Null Identity(1,1),
-- CustomerType Int Not Null Default(0),
Constraint [PK_Customers] Primary Key(CustomerID)
)
go
At this design, Orders only accept registered Customer, but I want Non Registered Customer can place Order, so I add CustomerType flag column (for Customer = 0, Non Customer = 1), is this approach practical ?
There is no need to add another column for this at all. Your Orders table as you defined it has a CutomerID, change that allow NULL and you don't need the CustomerType. If there is a CustomerID they are registered, if it is NULL they are not registered.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2012 at 10:54 am
Sean Lange (9/22/2012)
F8R (9/22/2012)
Ok, I'm hijacking my own thread, 😀At this design, Orders only accept registered Customer, but I want Non Registered Customer can place Order, so I add CustomerType flag column (for Customer = 0, Non Customer = 1), is this approach practical ?
There is no need to add another column for this at all. Your Orders table as you defined it has a CutomerID, change that allow NULL and you don't need the CustomerType. If there is a CustomerID they are registered, if it is NULL they are not registered.
That depends on what a customer is; sometimes if a customer is the thing that has a billing address, then if you want to have orders from non-registered customers who don't pay cash on order you need to have non-registered customers in the database. Equally if you link an invoice to a customer you need to have non-registered customers in the database. Personally I don't like the non-registered customer concept because recording a delivery address or a billing address probably needs a customer, and recording those addresses is registering the customer. You may well have to provide invoice details for tax purposes, and what tax is payable will often depend on where the order is delivered or on where it was billed. For example if I order something from a UK seller for delivery here I will not pay UK VAT on it; if I order something from a UK seller for delivery in the UK I will pay UK VAT on it, and the UK tax authorities, if they decide to audit the seller's tax declarations, will expect to be provided with full details for the relevant years' transactions which will include billing addresses and delivery addresses.
Tom
September 22, 2012 at 11:11 am
CELKO (9/22/2012)
This is a very different kind of programming. I think that 80-95% of the work is in the DDL (CREATE TABLE, etc), not in the DML (queries, etc). If you think we are weird, talk to an APL or LISP programmer:-D
I agree that relational programming would be very different from writing .net apps, but I'm not really sure you can do relational programming in SQL; for example I can't cleanly express cross-database foreign key constraints (writing a trigger instead of a constraint is NOT clean), nor can I express subset constraints cleanly (again SQL can only do it with triggers, ie in DML not in DDL). But yes, even SQL is quite different from .net development languages. And I think that maybe the proportion of DML in the total SQL work is usually higher than 20% (partly because SQL doesn't adequately support the relational model).
The weirdest thing about SQL is that people persist in claiming it's a declarative language, which I find quite a remarkable claim (although DDL is mostly conceptually declarative, DDML most certainly is not); APL is probably a bit weirder than SQL, but I thik LISP is slightly less weird; but none of these languages comes anywhere near the bizarre and insane weirdness of C++.
edit: D -> DM where original was nonsense.
Tom
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply