June 3, 2002 at 3:15 pm
Do you know if there is any way to use a trigger to prevent an update and present a message, but not use a rollback? I know I set the column to required I wouldn't need the trigger, but there are other ramifications to this method that I want to avoid. I'm using SQL Server 6.5. Thanks for your help. PC
June 3, 2002 at 3:52 pm
Not that I know of. With SQL2K you could use an instead of trigger, thats about the only way I know. Beyond that you could maybe figure out what was changed and reverse it out inside the trigger.
Andy
June 3, 2002 at 4:49 pm
Haven't tested, but I assume you could use Deleted and reset the value(s) you want. Not sure I'd recommend this. Why?
Steve Jones
June 3, 2002 at 4:59 pm
Thanks for the input.
What I'm trying to do is force the user to enter a reason why the record was changed. Then I want to copy this reason, along with the userid and date into a change history table, then reset the records change_reason field to null. The problem with making change_reason a required field is I can't wipe out the last reason. If I don't wipe it out the lazy users will just save the record without changing the reason.
PC
June 3, 2002 at 5:31 pm
Probably better off trying to enforce this in your app, just have the trigger do the logging. You could then run a job against that table to look for instances where the reason was left blank, have some kind of follow up system.
Andy
June 3, 2002 at 5:50 pm
You could enforce them making an update to the field, though I think Andy has the best solution. Check in the trigger to see if they changed anything. If not, then rollback the item and force them to resubmit.
Steve Jones
June 3, 2002 at 6:00 pm
I agree that the best way would be to have the trigger rollback if change_reason field was not updated, but I was trying to avoid the rollback because ALL data updated is lost and has to be entered again.
June 3, 2002 at 6:09 pm
Technically your app should be designed to rmemeber the last input and force them to enter a reason there. The only reason you would put this kind of business logic on the tables is when direct DB access is involved. For example I have several ASP pages that validate user input and if I am missing a key piece of input I redirect to a page to output their input and a not on required fields. In that case apps like EM also do not wipe out the input screen until the execution completes without error. Updates can only be lost then if the user purposely does not resubmit. The key is how is data input done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply