c# - SQL触发器试图插入一个空值，但是我的C#代码正在传递int 300吗？

我需要对我在扳机上犯的错误有一点清醒的认识。我在我的asp.net/c应用程序中使用了linq to sql数据层，在添加此触发器之前，它可以很好地将数据插入到数据库中。我知道传递到数据层的值是完全有效的，因为我也在调试器中检查过它们。我试图评估里程费用是否超过每年（英国纳税年度）10000英镑的限额，并根据不同的索赔率进行相应的分摊。这是我的扳机 USE [Horizon]GO/****** Object: Trigger [dbo].[trER_InsteadOf_Insert] Script Date: 07/22/2011 12:21:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 INSERTASBEGIN -- 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 ENDEND很抱歉粘贴了这么多代码，但我在这里遇到麻烦，但我的经验不足显然造成了这个错误。你能指出我不能指出的问题吗？谢谢 最佳答案 你的触发器中有这一行：SET @Mileage = (SELECT Mileage FROM WorkerSettings WHERE Worker_ID = @WorkerID)因此，如果 >为 >或不存在，那么，即使您已经传递了一个适当的值，也将以@WorkerID结束。这就是为什么null在没有触发器的情况下工作。