score:7

Accepted answer

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.

score:1

check that milage in workersettings for the workerid associated with the expenseid being inserted is not null.

score: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.


Related Query