1

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
  • This might be the answer I'm not sure, but could the [OrderCharge] table not have a value yet? – zerodoc Feb 17 '17 at 19:39
  • Are you answering your own question? – Erik Darling Feb 17 '17 at 19:40
  • 3
    There being a trigger involved is one of those important facts that you want to include in your question. It also helps if you supply some sample data. Walls of ANSI spaghetti code doesn't usually give too many clues as to why results are incorrect. – Erik Darling Feb 17 '17 at 20:20
  • 1
    Does the [OrderCharge] table have a matching value at this point? Or not? If it's empty or doesn't match the "inserted" row, then left-joining to it will give a NULL result so then you won't get the expected subtotal. – seventyeightist Feb 17 '17 at 21:01
  • 2
    Please post a complete example that demonstrates the issue you are asking about (with CREATE TABLE and INSERT statements) – Martin Smith Feb 18 '17 at 16:12
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.