This AFTER INSERT TRIGGER always evaluates to OrderTotal
ALTER TRIGGER [dbo].[OrderInformationInsert]
ON [dbo].[Order]
AFTER INSERT
AS
INSERT INTO [FRTOOLS_Orders]
(OrderNumber,
SubTotal)
SELECT i.[OrderNumber],
CASE
WHEN SUM(oc.Amount) > 0
THEN i.OrderTotal - SUM(oc.Amount)
ELSE i.OrderTotal
END AS subTotal
FROM inserted i
LEFT JOIN [OrderCharge] oc
ON i.OrderID = oc.OrderID
WHERE NOT EXISTS(SELECT '1'
FROM [FRTOOLS_Orders]
WHERE OrderNumber = i.[OrderNumber])
GROUP BY i.[OrderNumber],
i.[OrderTotal]
When I run the the select statement. I get the information as expected?
SELECT o.[OrderNumber],
CASE
WHEN SUM(oc.Amount) > 0
THEN o.OrderTotal - SUM(oc.Amount)
ELSE o.OrderTotal
END AS subTotal
FROM [Order] o
LEFT JOIN [OrderCharge] oc
ON o.OrderID = oc.OrderID
GROUP BY o.[OrderNumber],
o.[OrderTotal]
Would this be the case if the OrderCharge table has no values yet and how does FROM INSERTED work, Should I be going at this in a different direction.
OrderCharge Table Scripted out:
CREATE TABLE [dbo].[OrderCharge](
[OrderChargeID] [bigint] IDENTITY(1021,1000) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[OrderID] [bigint] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](255) NOT NULL,
[Amount] [money] NOT NULL,
CONSTRAINT [PK_OrderCharge] PRIMARY KEY CLUSTERED
(
[OrderChargeID] 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
ALTER TABLE [dbo].[OrderCharge] WITH CHECK ADD CONSTRAINT [FK_OrderCharge_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO
ALTER TABLE [dbo].[OrderCharge] CHECK CONSTRAINT [FK_OrderCharge_Order]
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderCharge', @level2type=N'COLUMN',@level2name=N'Amount'
GO
I'm not sure but Order Table I very long? By the way this is not our Database this part of Shipworks software and yes they named a table a sql keyword which is why we have to manipulate the table.
USE [ShipWorks]
GO
/****** Object: Table [dbo].[Order] Script Date: 2/18/2017 11:23:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
[OrderID] [bigint] IDENTITY(1006,1000) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[StoreID] [bigint] NOT NULL,
[CustomerID] [bigint] NOT NULL,
[OrderNumber] [bigint] NOT NULL,
[OrderNumberComplete] [nvarchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[OrderTotal] [money] NOT NULL,
[LocalStatus] [nvarchar](100) NOT NULL,
[IsManual] [bit] NOT NULL,
[OnlineLastModified] [datetime2](7) NOT NULL,
[OnlineCustomerID] [sql_variant] NULL,
[OnlineStatus] [nvarchar](100) NOT NULL,
[OnlineStatusCode] [sql_variant] NULL,
[RequestedShipping] [nvarchar](50) NOT NULL,
[BillFirstName] [nvarchar](30) NOT NULL,
[BillMiddleName] [nvarchar](30) NOT NULL,
[BillLastName] [nvarchar](30) NOT NULL,
[BillCompany] [nvarchar](60) NOT NULL,
[BillStreet1] [nvarchar](60) NOT NULL,
[BillStreet2] [nvarchar](60) NOT NULL,
[BillStreet3] [nvarchar](60) NOT NULL,
[BillCity] [nvarchar](50) NOT NULL,
[BillStateProvCode] [nvarchar](50) NOT NULL,
[BillPostalCode] [nvarchar](20) NOT NULL,
[BillCountryCode] [nvarchar](50) NOT NULL,
[BillPhone] [nvarchar](25) NOT NULL,
[BillFax] [nvarchar](35) NOT NULL,
[BillEmail] [nvarchar](100) NOT NULL,
[BillWebsite] [nvarchar](50) NOT NULL,
[BillAddressValidationSuggestionCount] [int] NOT NULL,
[BillAddressValidationStatus] [int] NOT NULL,
[BillAddressValidationError] [nvarchar](300) NOT NULL,
[BillResidentialStatus] [int] NOT NULL,
[BillPOBox] [int] NOT NULL,
[BillUSTerritory] [int] NOT NULL,
[BillMilitaryAddress] [int] NOT NULL,
[ShipFirstName] [nvarchar](30) NOT NULL,
[ShipMiddleName] [nvarchar](30) NOT NULL,
[ShipLastName] [nvarchar](30) NOT NULL,
[ShipCompany] [nvarchar](60) NOT NULL,
[ShipStreet1] [nvarchar](60) NOT NULL,
[ShipStreet2] [nvarchar](60) NOT NULL,
[ShipStreet3] [nvarchar](60) NOT NULL,
[ShipCity] [nvarchar](50) NOT NULL,
[ShipStateProvCode] [nvarchar](50) NOT NULL,
[ShipPostalCode] [nvarchar](20) NOT NULL,
[ShipCountryCode] [nvarchar](50) NOT NULL,
[ShipPhone] [nvarchar](25) NOT NULL,
[ShipFax] [nvarchar](35) NOT NULL,
[ShipEmail] [nvarchar](100) NOT NULL,
[ShipWebsite] [nvarchar](50) NOT NULL,
[ShipAddressValidationSuggestionCount] [int] NOT NULL,
[ShipAddressValidationStatus] [int] NOT NULL,
[ShipAddressValidationError] [nvarchar](300) NOT NULL,
[ShipResidentialStatus] [int] NOT NULL,
[ShipPOBox] [int] NOT NULL,
[ShipUSTerritory] [int] NOT NULL,
[ShipMilitaryAddress] [int] NOT NULL,
[RollupItemCount] [int] NOT NULL,
[RollupItemName] [nvarchar](300) NULL,
[RollupItemCode] [nvarchar](300) NULL,
[RollupItemSKU] [nvarchar](100) NULL,
[RollupItemLocation] [nvarchar](255) NULL,
[RollupItemQuantity] [float] NULL,
[RollupItemTotalWeight] [float] NOT NULL,
[RollupNoteCount] [int] NOT NULL,
[BillNameParseStatus] [int] NOT NULL,
[BillUnparsedName] [nvarchar](100) NOT NULL,
[ShipNameParseStatus] [int] NOT NULL,
[ShipUnparsedName] [nvarchar](100) NOT NULL,
[ShipSenseHashKey] [nvarchar](64) NOT NULL,
[ShipSenseRecognitionStatus] [int] NOT NULL,
[ShipAddressType] [int] NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] 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
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Store] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Store] ([StoreID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Store]
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Store' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Customer' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Order Number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumberComplete'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderTotal'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineCustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineStatusCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillNameParseStatus'
GO
EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipNameParseStatus'
GO
Best Answer
It appears that the system does an INSERT into the Order table and then an INSERT into the OrderCharge table.
If you want to capture information about the charges, you will need to do it after the INSERT in the OrderCharge table.