Trigger to Only Allow a Single Null Value Per Item ID
I am working on a database for equipment management, where users can sign in and out equipment. I have a table called TBL_Usage which keeps track of this. When an item is signed out, a new record is created in this table with the sign out date, and when it is signed back in, a sign in date is entered in the same record.
For the purposes of this question, the table looks like this:
TBL_Usage(Date_Out, Date_In, Equipment_ID)
Now an item can’t be signed out twice at the same time, therefore for each Equipment_ID, there can only be a maximum of one record where Date_In is null. I want to create a trigger/constraint to deal with this and am not quite sure how to set it up.
One Solution collect form web for “Trigger to Only Allow a Single Null Value Per Item ID”
You can do it with a filtered unique index.
create unique index IX_TBL_Usage_Equipment_ID on TBL_Usage(Equipment_ID) where Date_In is null