主页 / dba / 问题 / 164739
Accepted
zerodoc
Asked: 2017-02-18 11:35:54 +0800 CST

在插入语句中使用时，CASE WHEN 始终评估 ELSE 值

  • 772

此 AFTER INSERT TRIGGER 始终评估为 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]

当我运行 select 语句时。我得到了预期的信息？

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]

如果 OrderCharge 表还没有值以及 FROM INSERTED 是如何工作的，是否会出现这种情况，我是否应该朝不同的方向前进。

OrderCharge 表编写脚本：

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

我不确定，但 Order Table 我很长？顺便说一句，这不是我们的数据库，这部分是 Shipworks 软件，是的，他们将表命名为 sql 关键字，这就是我们必须操作该表的原因。

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
sql-server t-sql

1 个回答

  1. Best Answer

    看起来系统在 Order 表中执行了一个 INSERT，然后在 OrderCharge 表中执行了一个 INSERT。

    如果您想捕获有关费用的信息，您需要在 OrderCharge 表中的 INSERT 之后执行此操作。

    • 1

相关问题