We have a requirement for which we need to ensure that in a set of rows at least one of the rows does not have an end-date. In below example it should not be allowed to add an end-date to test=3.
Best answer by Robert Jan de Nie
View originalWe have a requirement for which we need to ensure that in a set of rows at least one of the rows does not have an end-date. In below example it should not be allowed to add an end-date to test=3.
Best answer by Robert Jan de Nie
View originalTechnically I think you could implement that in a constraint using a function. Downside however is that this is not properly supported by the Software Factory. It will lead to this function not being able to be modified without dropping the table / constraint that is being used.
What I’d do is implement this in a trigger. The update-trigger could look something like this:
if exists (select *
from inserted i
join deleted d
on d.vessel_pk = i.vessel_pk
where d.end_date is null --check if there is a situation where there was no end-date
and i.end_date is not null --and this end-date has been updated to a non-empty value
and not exists (select * --where there are currently no non-end-dated vessels left of that perticular vessel_id
from vessel v
where v.vessel_id = i.vessel_id
and v.end_date is null
)
)
begin
exec tsf_send_message at_least_one_vessel_should_have_no_end_date, null, 1
end
Equally, you need to implement a trigger on the delete event in order to prevent a user deleting the last vessel record without an end-date.
Hope this makes sense?
(edit: sorry for the messed up layout of the SQL, paste it into Management Studio in order to have propper syntax highlighting)
This certainly makes sense. Thanks Robert!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.