Best way to prevent Update

Question

Hi All,

I have a situation where we want to prevent the update of a table for a specific scenario.

So for 95% of the updates I would want the update to flow through as normal and update the desired fields....for that other 5% I want to prevent the update from happening. This will be based on data passed in the update along with what is currently populated in the table.

So a good example is a user submitted their time for today. If they resubmit their time I don't want the DB to allow that update to occur because the time table for that user has it flagged that they already submitted their time and the time field is NOT NULL.

I was playing with triggers for this and it appears the INSTEAD OF trigger would allow me to handle this...basically only let the update occur if that criteria holds true.

Any thoughts? I appreciate any ideas.

(Let me know if this is clear)

EDIT: I apologize...that was probably not a good example. We reuse an update sproc to perform many different updates so it is fairly generic. I wanted to figureout what the best approach is for those cases where I don't want the update to be performed. Using a trigger was basically a safeguard to prevent someone from accidentally overwriting previously entered data. Thanks for the input

*EDIT2:*Thanks All...after thinking through it Martin is right I should just build the logic into the sproc. I was told that they wanted to use a trigger to handle this, but you are all correct...that is just not the right way to handle this. Thanks again for your help.

Thanks,

S

Answer 1

You don't need a trigger for this. e.g. something like

UPDATE Users 
 SET timefield = @timefield
WHERE UserId = @UserId AND [Day]=@day AND timefield IS NULL

Would work for your example. You could then check the rows affected to see whether anything was actually updated or not.