Check your default constraints

Question

Check your default constraints

allinadazework

SSCarpal Tunnel

Points: 4506
More actions
September 16, 2018 at 11:38 pm

#415646

Comments posted to this topic are about the item Check your default constraints

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 1

Perhaps this seems a bit simplistic!? I just thought it would be useful to share this simple design idea with the community. I'll be adding checks to my defaults from now on after having issues with some some default dates being overridden by developers. It's not the perfect solution. Let me know if you have other solutions for this issue.

vern@rabe.net SSC Enthusiast Points: 119 More actions · Answer 2

That's a simple solution, but it's not comprehensive. Someone can update a row, specifying GETDATE() for the LogAdded column, and the row will get updated. The LogAdded column will then have the timestamp that the row was updated, not when in was inserted. I believe one comprehensive solution is to add an insert/update trigger with this code:

IF UPDATE(LogAdded) ROLLBACK;

Of course you could have more elaborate error handling than just a ROLLBACK;

michael.cole 47030 SSC Eights! Points: 804 More actions · Answer 3

allinadazework - Monday, September 17, 2018 4:47 AM
Perhaps this seems a bit simplistic!? I just thought it would be useful to share this simple design idea with the community. I'll be adding checks to my defaults from now on after having issues with some some default dates being overridden by developers. It's not the perfect solution. Let me know if you have other solutions for this issue.

Or a better idea if it is developers who are the problem, then don't allow them acces to the field in the first place. All table updating/insertions is done through stored procedures, and those SPs do not expose the audit fields for changes.

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 4

michael.cole 47030 - Monday, September 17, 2018 7:51 PM
allinadazework - Monday, September 17, 2018 4:47 AM
Perhaps this seems a bit simplistic!? I just thought it would be useful to share this simple design idea with the community. I'll be adding checks to my defaults from now on after having issues with some some default dates being overridden by developers. It's not the perfect solution. Let me know if you have other solutions for this issue.
Or a better idea if it is developers who are the problem, then don't allow them acces to the field in the first place. All table updating/insertions is done through stored procedures, and those SPs do not expose the audit fields for changes.

Hi Michael, I think I did suggest that in an ideal world you would have this table only added to by a single stored procedure that only allows the default value with the associated security. So this was just a suggestion to help prevent data issues where it is not practical to impose the sort of security you suggest. Although I completely agree that locking it down with access via a single SP and appropriate security would be the ideal solution.

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 5

vern@rabe.net - Monday, September 17, 2018 9:53 AM
That's a simple solution, but it's not comprehensive. Someone can update a row, specifying GETDATE() for the LogAdded column, and the row will get updated. The LogAdded column will then have the timestamp that the row was updated, not when in was inserted. I believe one comprehensive solution is to add an insert/update trigger with this code:
IF UPDATE(LogAdded) ROLLBACK;
Of course you could have more elaborate error handling than just a ROLLBACK;

Yes, good point! I hadn't thought of that. Although arguably if someone is updating the log record then that is still the correct log update date. If the column should contain the date the row was inserted though you would definitely want to prevent further updates after the initial insert. Thanks for the suggestion.