SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

zerodoc

Asked: 2017-02-18 11:35:54 +0800 CST2017-02-18 11:35:54 +0800 CST 2017-02-18 11:35:54 +0800 CST

CASE WHEN está sempre avaliando o valor ELSE quando usado em uma instrução de inserção

772

Este AFTER INSERT TRIGGER sempre avalia como 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]

Quando eu executo a instrução select. Eu recebo as informações como esperado?

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]

Seria este o caso se a tabela OrderCharge ainda não tiver valores e como FROM INSERTED funciona , devo estar indo em uma direção diferente.

Tabela OrderCharge com script:

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

Não tenho certeza, mas Order Table I muito longo? A propósito, este não é o nosso banco de dados, esta parte do software Shipworks e sim, eles nomearam uma tabela com uma palavra-chave sql, e é por isso que temos que manipular a tabela.

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

1 respostas

Voted

Cade Roux · Answer 1 · 2017-02-21T08:29:04+08:00

Best Answer

Cade Roux

2017-02-21T08:29:04+08:002017-02-21T08:29:04+08:00

Parece que o sistema faz um INSERT na tabela Order e, em seguida, um INSERT na tabela OrderCharge.

Se você deseja capturar informações sobre as cobranças, precisará fazê-lo após o INSERT na tabela OrderCharge.

1

CASE WHEN está sempre avaliando o valor ELSE quando usado em uma instrução de inserção

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

CASE WHEN está sempre avaliando o valor ELSE quando usado em uma instrução de inserção

1 respostas

relate perguntas