Easy Trigger question

I would imagine that this would be an easy question for someone who works a lot with T-SQL and especially Triggers:

I want to enforce the following constraints on all updates and inserts to this table:

1. If DiscountTypeId = 1, then FlatFee must not be NULL.
2. If DiscountTypeId = 2, then DiscountRate must not be null.

If either one of these two conditions fail on an insert or update to the table, I'd like to return an appropriate error.

The trigger appears not to do anything yet. .Can you provide the necessary changes so it performs as described?

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

EDIT

I got it to work. I haven't read up on Triggers to remedy my fundamental lack of understanding, but t his seemed to work, although I believe that the Check Constraint is the better approach:

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.

---

I'd use a CHECK constraint, not a triggers

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)
)

Also, need to consider "if DiscountTypeId <> 1, does Flatfee have to be NULL" etc

---

You fundamentally do not understand triggers. The very first thing you need to do is go read about triggers in Books Online with particular emphasis on learning about the inserted and deleted psuedotables. Next thing you need to know is a trigger should NEVER be written as if it will handle only one record at a time. Triggers operate on batches of records and trigger code must account for that.

---

I don't believe triggers can raise errors, problem #1.