sql-server - 轻松触发问题

Question

我可以想象，对于那些使用T-SQL尤其是Triggers的人来说，这将是一个简单的问题:

我要在此表的所有更新和插入上强制执行以下约束:

如果DiscountTypeId = 1，则FlatFee不能为NULL。

如果DiscountTypeId = 2，则DiscountRate不能为null。

如果这两个条件之一在插入或更新表时失败，我想返回一个适当的错误。

触发器似乎尚未执行任何操作。您能否提供必要的更改，使其按说明执行？

USE [PandaVisa2008]
GO

/****** Object:  Table [dbo].[CustomerSpeed]    Script Date: 11/04/2010 15:51:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerSpeed](
 [CustomerSpeedId] [int] NOT NULL,
 [CustomerId] [int] NULL,
 [SpeedId] [int] NOT NULL,
 [DiscountTypeId] [int] NOT NULL,
 [FlatFee] [money] NULL,
 [DiscountRate] [decimal](3, 3) NULL,
 CONSTRAINT [PK_AgentFee] PRIMARY KEY CLUSTERED 
(USE [PandaVisa2008]

GO

/****** Object:  Trigger [dbo].[TRG_CustomerSpeed_OnInsertUpdate]    Script Date: 11/04/2010 15:38:06 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[TRG_CustomerSpeed_OnInsertUpdate]
ON [dbo].[CustomerSpeed]
FOR INSERT, UPDATE
AS
    BEGIN

        DECLARE @DiscountTypeId INT
        DECLARE @FlatFee MONEY
        DECLARE @DiscountRate DECIMAL(3, 3)

        SELECT
            @DiscountTypeId = DiscountTypeId,
            @FlatFee = FlatFee,
            @DiscountRate = DiscountRate
        FROM
            inserted

        IF @DiscountTypeId = 1
           AND @FlatFee IS NULL
            BEGIN
                RAISERROR (N'If @DiscountTypeId is 1, FlatFee must not be NULL',
                           10,
                           1)
            END

  IF @DiscountTypeId = 2
           AND @DiscountRate IS NULL
            BEGIN
                RAISERROR (N'If @DiscountTypeId is 2, @DiscountRate must not be NULL',
                           10,
                           1)
            END            
    END 

 [CustomerSpeedId] 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].[CustomerSpeed]  WITH CHECK ADD  CONSTRAINT [CK_CustomerSpeed] CHECK  (([DiscountRate]>(0) AND [DiscountRate]<(1)))
GO

ALTER TABLE [dbo].[CustomerSpeed] CHECK CONSTRAINT [CK_CustomerSpeed]
GO

编辑

我知道了。我还没有阅读过扳机来弥补我对根本的理解上的不足，但是尽管我相信“检查约束”是更好的方法，但他似乎可以奏效:

ALTER TRIGGER [dbo].[TRG_CustomerSpeed_OnInsertUpdate]
ON [dbo].[CustomerSpeed]
FOR INSERT, UPDATE
AS
    BEGIN
        IF EXISTS (SELECT
                       1
                   FROM
                       inserted I
                   WHERE  I.DiscountTypeId = 1
                      AND I.FlatFee IS NULL)
            BEGIN
                ROLLBACK TRANSACTION

                RAISERROR (N'If DiscountTypeId is 1, FlatFee must not be NULL',
                           10,
                           1)
            END

       IF EXISTS (SELECT
                       1
                   FROM
                       inserted I
                   WHERE  I.DiscountTypeId = 2
                      AND I.DiscountRate IS NULL)
            BEGIN
                ROLLBACK TRANSACTION

                RAISERROR (N'If DiscountTypeId is 2, DiscountRate must not be NULL',
                           10,
                           1)
            END            
    /*
     IF @DiscountTypeId = 2
        AND @DiscountRate IS NULL
         BEGIN
         Rollback Transaction
             RAISERROR (N'If @DiscountTypeId is 2, DiscountRate must not be NULL',
                        10,
                        1)
         END
    */
    END 

Your comments are welcomed.

Answer 1

我会使用CHECK约束，而不是触发器

ALTER TABLE Mytable WITH CHECK ADD
   CONSTRAINT CK_MyTable_GoodName CHECK (
        NOT (DiscountTypeId = 1 AND Flatfee IS NULL)
        AND
        NOT (DiscountTypeId = 2 AND DiscountRate IS NULL)
)

另外，需要考虑“如果DiscountTypeId <> 1，则Flatfee是否必须为NULL”等