July 24, 2012 at 6:59 pm
I have three tables one called PONumber which has PONumber and Subtotal another table called PurchaseOrderHeader and one called PurchaseOrderDetail the PurchaseOrderDetail it has Qty, Price, shipping, and total adds up automatically Im wanting a Trigger to update the PONumber Subtotal. The PurchasedOrderDetail has multiple lines for the same PONumber that is why I am using the sum(total) as Subtotal which with out the trigger information it works right
--alter Trigger UPDate_PONumber
--On PurchaseOrderDetail
--for insert, Update, delete
--as
Declare @SubTotal decimal(14,2)
Select sum (Total) as SubTotal, pod.PurchaseOrderID, PON.PONumber , PON.SubTotal
from PurchaseOrderDetail as POD
inner Join PurchaseOrderHeader as POH
on POD.PurchaseOrderID = POH.PurchaseOrderID
Inner join PONumber as PON
on PON.PONumber = POH.PONumber
where POD.PurchaseOrderID = POD.PurchaseOrderID
Group by POD.PurchaseOrderID, pod.PurchaseOrderID, PON.PONumber, PON.SubTotal
if (@SubTotal!=0)
Begin
update PONumber
set SubTotal = @SubTotal
where PONumber = PONumber
End
July 24, 2012 at 8:05 pm
If you provide DDL and sample data for your PO Header and Detail tables, I or someone else can make short work of this and give you a tested solution.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 9:08 pm
Here is the script for the database.
July 24, 2012 at 9:11 pm
edward_hall76 (7/24/2012)
Here is the script for the database.
I think you may have forgotten to include the attachment?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2012 at 9:04 am
edward_hall76 (7/24/2012)
Here is the script for the database.
Holy cow. We don't need the entire database just the stuff relevant to your issue. In the future it would probably be in your best interest to post the code using IFCode shortcuts (over there on the left when you are posting) instead of a Word doc.
I took the liberty of retrieving the relevant tables from your description.
CREATE TABLE [dbo].[PONumber](
[PONumber] [int] NOT NULL,
[SubTotal] [decimal](14, 2) NULL,
CONSTRAINT [PK_PONumber] PRIMARY KEY CLUSTERED
(
[PONumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PurchaseOrderHeader](
[PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
[PONumber] [int] NULL,
[VendorID] [int] NULL,
[ApporvedBy] [varchar](50) NULL,
[ApporvedDate] [date] NULL,
[DateModified] [datetime] NULL,
[DeptID] [int] NULL,
[AccountNumber] [varchar](50) NULL,
CONSTRAINT [PK_PurchaseOrderHeader] PRIMARY KEY CLUSTERED
(
[PurchaseOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PurchaseOrderDetail](
[PurchaseOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderID] [int] NULL,
[DeptID] [varchar](50) NULL,
[AccountNumber] [varchar](50) NULL,
[PODate] [date] NULL,
[Product] [varchar](50) NULL,
[QTY] [int] NULL,
[Price] [decimal](14, 2) NULL,
[Shipping] [decimal](14, 2) NULL,
[Total] AS ([QTY]*[Price]+[Shipping])
) ON [PRIMARY]
GO
OK so now we have tables but we still don't have any data to work with. See the first link in my signature about best practices when posting questions.
In looking at your trigger I am totally confused what you are trying to do. You define a @SubTotal variable but never set a value. You never reference the inserted table in your code.
if (@SubTotal!=0)
Begin
update PONumber
set SubTotal = @SubTotal
where PONumber = PONumber
I think I understand what you are trying to accomplish. Let me know if I am incorrect but I think you want to update PONumber and set the SubTotal = sum of Total from PurchaseOrderDetail?
This is certainly obtainable but is one the main reasons it is frowned on to store calculated data like this. I would suggest you create a view instead of a permanent table with calculated totals.
Something like this:
create view PONumber
as
select poh.PONumber, SUM(pod.Total) as SubTotal
from PurchaseOrderHeader poh
join PurchaseOrderDetail pod on poh.PurchaseOrderID = pod.PurchaseOrderID
group by poh.PONumber
This way you don't have to worry about your trigger and the possible performance issues along with possible data issues from not having your calculated data updated correctly.
_______________________________________________________________
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/
July 25, 2012 at 9:26 am
He is the information in the database.
On a view will it update the PONumber table everytime something is updated or added to the PurhcaseOrderDetail
July 25, 2012 at 9:30 am
edward_hall76 (7/25/2012)
He is the information in the database.On a view will it update the PONumber table everytime something is updated or added to the PurhcaseOrderDetail
I don't understand your response here. A view doesn't update anything. It would be used to get current information instead of trying to store calculated data. Storing calculated data like you are trying to do is fraught with issues. Your example is text book case of why you should use views.
_______________________________________________________________
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/
July 25, 2012 at 9:43 am
I think the code below should at least be close to what you need (hopefully very close :-)):
ALTER TRIGGER UPDate_PONumber
ON dbo.PurchaseOrderDetail
AFTER INSERT, UPDATE, DELETE
AS
UPDATE PON
SET SubTotal = PO_Total.SubTotal
FROM dbo.PONumber AS PON
INNER JOIN (
SELECT
SUM(Total) AS SubTotal, POH.PONumber
FROM dbo.PurchaseOrderDetail AS POD
INNER JOIN inserted AS i
ON i.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN dbo.PurchaseOrderHeader AS POH
ON POH.PurchaseOrderID = POD.PurchaseOrderID
GROUP BY
POH.PONumber
) AS PO_Total
ON PON2.PONumber = PO_Total.PONumber
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2012 at 9:49 am
ScottPletcher (7/25/2012)
I think the code below should at least be close to what you need (hopefully very close :-)):
ALTER TRIGGER UPDate_PONumber
ON dbo.PurchaseOrderDetail
AFTER INSERT, UPDATE, DELETE
AS
UPDATE PON
SET SubTotal = PO_Total.SubTotal
FROM dbo.PONumber AS PON
INNER JOIN (
SELECT
SUM(Total) AS SubTotal, POH.PONumber
FROM dbo.PurchaseOrderDetail AS POD
INNER JOIN inserted AS i
ON i.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN dbo.PurchaseOrderHeader AS POH
ON POH.PurchaseOrderID = POD.PurchaseOrderID
GROUP BY
POH.PONumber
) AS PO_Total
ON PON2.PONumber = PO_Total.PONumber
GO
This does not handle deletes though. 😉 You need to subtract when deleting.
OP - What do you want to happen to that table if you delete the last detail? Do you want to leave orphans or delete it?
_______________________________________________________________
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/
July 25, 2012 at 10:12 am
Sean Lange (7/25/2012)
ScottPletcher (7/25/2012)
I think the code below should at least be close to what you need (hopefully very close :-)):
ALTER TRIGGER UPDate_PONumber
ON dbo.PurchaseOrderDetail
AFTER INSERT, UPDATE, DELETE
AS
UPDATE PON
SET SubTotal = PO_Total.SubTotal
FROM dbo.PONumber AS PON
INNER JOIN (
SELECT
SUM(Total) AS SubTotal, POH.PONumber
FROM dbo.PurchaseOrderDetail AS POD
INNER JOIN inserted AS i
ON i.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN dbo.PurchaseOrderHeader AS POH
ON POH.PurchaseOrderID = POD.PurchaseOrderID
GROUP BY
POH.PONumber
) AS PO_Total
ON PON2.PONumber = PO_Total.PONumber
GO
This does not handle deletes though. 😉 You need to subtract when deleting.
OP - What do you want to happen to that table if you delete the last detail? Do you want to leave orphans or delete it?
Good point about DELETEs -- remove DELETE from the trigger actions:
AFTER INSERT, UPDATE
and use a separate trigger for that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2012 at 10:47 am
ScottPletcher (7/25/2012)
Sean Lange (7/25/2012)
ScottPletcher (7/25/2012)
I think the code below should at least be close to what you need (hopefully very close :-)):
ALTER TRIGGER UPDate_PONumber
ON dbo.PurchaseOrderDetail
AFTER INSERT, UPDATE, DELETE
AS
UPDATE PON
SET SubTotal = PO_Total.SubTotal
FROM dbo.PONumber AS PON
INNER JOIN (
SELECT
SUM(Total) AS SubTotal, POH.PONumber
FROM dbo.PurchaseOrderDetail AS POD
INNER JOIN inserted AS i
ON i.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN dbo.PurchaseOrderHeader AS POH
ON POH.PurchaseOrderID = POD.PurchaseOrderID
GROUP BY
POH.PONumber
) AS PO_Total
ON PON2.PONumber = PO_Total.PONumber
GO
This does not handle deletes though. 😉 You need to subtract when deleting.
OP - What do you want to happen to that table if you delete the last detail? Do you want to leave orphans or delete it?
Good point about DELETEs -- remove DELETE from the trigger actions:
AFTER INSERT, UPDATE
and use a separate trigger for that.
Why? You can do an
IF (SELECT COUNT(*) FROM INSERTED > 0) for inserts and updates and then ELSE for deletes.
Jared
CE - Microsoft
July 25, 2012 at 10:58 am
All this work and effort to store a piece of data that should be retrieved real time using a view instead. 😛
I would comment on the table structure a little bit. You have PONumber in your PONumber table. This column is not unique in the PurchaseOrderHeader, and shouldn't be unique. However it is effectively the PK of the PONumber table. It should instead have PurchaseOrderID and then add an actual foreign key to PurchaseOrderHeader. The other challenge is your PONumber is an int. Unless this is holding ONLY PONumbers from your system you will have issues with that. There are plenty of companies out there that use character data in their PONum.
I am not quite sure why you have DeptID, AccountNumber and PODate in the detail table. Those all look like they belong to the entire PO and not the line items. Of course I may be wrong on these since you have AccountNumber in both the header and the detail tables.
_______________________________________________________________
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/
July 25, 2012 at 1:12 pm
Thats working ok except if there is already a amount in the subtotal in the PONumber table then it doesnt add them together.
I also get a error if trying to insert data into the table using another software as a frount end.
July 25, 2012 at 1:21 pm
The reason for account number being the line item is because each office has there own budget and they are broken into computer supplies, office furnture, copy supplies etc and each one has its on accoun number like xxx-000-yyy so we would have to show which account it is comeing out of for the same po or they would have to fill out multuple pos.
July 25, 2012 at 1:22 pm
edward_hall76 (7/25/2012)
Thats working ok except if there is already a amount in the subtotal in the PONumber table then it doesnt add them together.I also get a error if trying to insert data into the table using another software as a frount end.
That is because the trigger as posted does not do what you want/need it to. The update statement should be
UPDATE PON
SET SubTotal = PON.SubTotal + PO_Total.SubTotal
...
As for the error I am guessing your insert statement has an OUTPUT clause? Here is a link that explains the condition and at least one way to work around it. http://beyondrelational.com/modules/2/blogs/115/posts/11166/output-clause-and-triggers.aspx
OK so I have to ask. I have suggested multiple times using a view for this but you are still fighting a battle to make this into a trigger. What is wrong with doing this the easy way?
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply