简单触发问题

Question

我想，对于经常使用 T-SQL（尤其是触发器）的人来说，这将是一个简单的问题：

我想对该表的所有更新和插入强制执行以下约束：

1. 如果 DiscountTypeId = 1，则不得使用 FlatFee无效的。
2. 如果 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”等

Answer 2

你从根本上不了解触发器。您需要做的第一件事是阅读在线书籍中有关触发器的内容，特别强调了解插入和删除的伪表。接下来您需要知道的是，决不应该编写触发器，就好像它一次只能处理一条记录一样。触发器对批量记录进行操作，并且触发器代码必须考虑到这一点。

Answer 3

我不相信触发器会引发错误，问题#1。