SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Trigger To Update Total On 'Parent' Table


Creating Trigger To Update Total On 'Parent' Table

Author
Message
F8R
F8R
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16736 Visits: 19115
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
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.

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)
F8R
F8R
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 39
Thank you for all hint and sugestion, I think I need to unlearn my 'old' knowledge, BigGrin.

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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
F8R (9/20/2012)
Thank you for all hint and sugestion, I think I need to unlearn my 'old' knowledge, BigGrin.

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.

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)
F8R
F8R
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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, BigGrin.

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.
F8R
F8R
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
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.

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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search