SQL Trigger is trying to insert a null value but my C# code is passing int 300?

Question

I need a little heads up on the mistake I have made on this trigger. I am using a LINQ to SQL data layer in my asp.net/c# application which inserts data into the database fine before I added this trigger.

I know that the values being passed down into the data layer are perfectly valid as I have checked them in the debugger also.

I am trying to evaluate if an expense for mileage exceeds an annual (uk tax year) limit of 10,000 and split it accordingly for different rates of the claim.

Here is my trigger

    USE [Horizon]
GO
/****** Object:  Trigger [dbo].[trER_InsteadOf_Insert]    Script Date: 07/22/2011 12:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Matthew Arnold
-- Create date: 19/07/2011
-- Description: Validate Expense Row for Allowed Mileage
-- =============================================
ALTER TRIGGER [dbo].[trER_InsteadOf_Insert] 
   ON  [dbo].[ExpenseRow] 
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @WorkerID varchar(20);
    DECLARE @ExpenseID int;
    DECLARE @Mileage int;
    DECLARE @Miles int;
    DECLARE @TotalMiles int;
    DECLARE @MilesOver10k int;
    DECLARE @MilesUnder10k int;
    DECLARE @Amount decimal (7, 2);
    DECLARE @AmountOver decimal(7, 2);
    DECLARE @ClaimedDate datetime;
    DECLARE @HighRateMileage decimal (7, 2);
    DECLARE @LowRateMileage decimal (7, 2);

    --Get current mileage and additional miles to work out threshold for worker expense
    SET @ExpenseID = (SELECT Expense_ID FROM inserted)
    SET @WorkerID = (SELECT Worker_ID FROM Expense WHERE Expense_ID = @ExpenseID)
    SET @Mileage = (SELECT Mileage FROM WorkerSettings WHERE Worker_ID = @WorkerID)
    SET @Miles = (SELECT Mileage FROM inserted)
    SET @ClaimedDate = (SELECT ExpenseDate From inserted)

    SET @HighRateMileage = (SELECT Value FROM dbo.SystemSettings WHERE ConfigType_ID = 1 
                            AND @ClaimedDate >=  StartDate
                            AND @ClaimedDate <= EndDate)
    SET @LowRateMileage =  (SELECT Value FROM dbo.SystemSettings WHERE ConfigType_ID = 2
                            AND @ClaimedDate >=  StartDate
                            AND @ClaimedDate <= EndDate)

    SET @TotalMiles = @Mileage + @Miles
    SET @MilesOver10k = @TotalMiles - 10000
    SET @MilesUnder10k = @Miles - @MilesOver10k

            IF (@Mileage < 10000)
                BEGIN
                    IF (@TotalMiles > 10000)
                        BEGIN
                            SET @AmountOver = @MilesOver10k * @LowRateMileage
                            SET @Amount = @MilesUnder10k * @HighRateMileage

                            --Split higher rate and lower rate mileage and insert two expense rows
                            INSERT INTO dbo.ExpenseRow (Expense_ID, JobNumber, ExpenseType_ID, Mileage,
                                                        ExpenseDate, Description, Amount, Notes, ClientChargeable,
                                                        VAT_Receipt, ItemAuthorised, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn)

                            SELECT Expense_ID, JobNumber, ExpenseType_ID, @MilesOver10k, ExpenseDate,
                                                        Description, @AmountOver, 'Calculated at' + ' ' + @LowRateMileage + ' ' + 'pence per mile', 
                                                        ClientChargeable,
                                                        VAT_Receipt, 0, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM inserted



                            INSERT INTO dbo.ExpenseRow (Expense_ID, JobNumber, ExpenseType_ID, Mileage,
                                                        ExpenseDate, Description, Amount, Notes, ClientChargeable,
                                                        VAT_Receipt, ItemAuthorised, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn)

                            SELECT Expense_ID, JobNumber, ExpenseType_ID, @MilesUnder10k, ExpenseDate,
                                                        Description, @Amount, 'Calculated at' + ' ' + @HighRateMileage + ' ' + 'pence per mile', 
                                                        ClientChargeable,
                                                        VAT_Receipt, 0, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM inserted


                        END
                    ELSE
                        BEGIN
                            --Insert higher rate into the expense row
                            INSERT INTO dbo.ExpenseRow (Expense_ID, JobNumber, ExpenseType_ID, Mileage,
                                                        ExpenseDate, Description, Amount, Notes, ClientChargeable,
                                                        VAT_Receipt, ItemAuthorised, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn)

                            SELECT Expense_ID, JobNumber, ExpenseType_ID, Mileage,ExpenseDate, 
                                                        Description, Amount, 'Calculated at' + ' ' + @HighRateMileage + ' ' + 'pence per mile', 
                                                        ClientChargeable,
                                                        VAT_Receipt, 0, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM inserted


                        END

                END


            ELSE 

                    BEGIN
                        SET @AmountOver = @MilesOver10k * @LowRateMileage

                            --Insert lower rate mleage into the expense row
                            INSERT INTO dbo.ExpenseRow (Expense_ID, JobNumber, ExpenseType_ID, Mileage,
                                                        ExpenseDate, Description, Amount, Notes, ClientChargeable,
                                                        VAT_Receipt, ItemAuthorised, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn)

                            SELECT Expense_ID, JobNumber, ExpenseType_ID, @Mileage,ExpenseDate, 
                                                        Description, @AmountOver, 'Calculated at' + ' ' + @LowRateMileage + ' ' + 'pence per mile', 
                                                        ClientChargeable,
                                                        VAT_Receipt, 0, AuthBy, 
                                                        CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM inserted


                    END




END

Sorry for pasting so much code but I am stumped here but my lack of experience has obviously created this error.

Can you spot my issue as I can't?

Thanks

Answer 1

You have this line in your trigger:

SET @Mileage = (SELECT Mileage FROM WorkerSettings WHERE Worker_ID = @WorkerID)

So if @WorkerID is null or doesn't exist, then @Mileage is going to end up being null even if you've passed in a proper value for it. This is why the INSERT works without the trigger.

Answer 2

Let me tell you how to best develop and debug triggers.

First create temptables for #inserted and/or #deleted'

Then insert mulitple records into the temp tables that would be the data that the trigger inserted and deleted would have. Make sure you cover all the test cases you need. It is critical that you have mulitple records.

Then write your code using #inserted instead of inserted.

Now you can run in steps and check data before the final action. You can also put the whole thing ina transaction that you can automatically rollback until you get the code right.

Finally, I said this in a comment, but never write a trigger assuming only one record at a time will be processed.

Once it's right, change the temp table names to inserted or deleted and add the create trigger code.