User Input after Insert Trigger

[memcom]

I have run into an obstacle with our ERP software (Commerce Center). Our software is an SQL 2000 based system with a front end created with PowerBuilder, which I cannot modify at all. My problem is that when someone tries to create a new Ship To record in the user interface, there is a particular field that is often left blank. Commerce Center does not require this field, but as a company policy I always want the field to be populated. I have tried changing the field to not null in enterprise manager, but because of the way commerce center is programmed, I get an error and it does not insert the record, but the user cannot go back and populate that field. I have tried a few other things, but ultimately the insert statement has to be allowed with corrective measures taken after the insert statement. I know I can create a trigger that will email the user who saved the record, but the user could then ignore the email. What I would like to have is an input box that will pop up on the user’s screen for them to enter the data for that field. I have no idea how to accomplish this. I tried to create a quick VB app that simply had a text box and submit button which would run an update statement. This VB app would then be executed through a trigger that used xp_cmdshell. I found that my program would run, but the interface was never seen. Does anyone have any suggestions for me?

Thanks,
Michael Dean

 

[druer]

Have you considered using a Default value on the field, and as you suggest combine that with an email back to the creator which says "You failed to input a value so it has been defaulted to "x". If that is acceptable you don't need to take any action, but if that is not acceptable you need to update that record.

It sounds like the problem is that the PowerBuilder application didn't account for a record not being inserted and it is very tough to accomodate errors in which the application doesn't correctly check for them, and allow the user to then take appropriate action.

My personal philosophy is that functionality that involves end users is best left to the project/functionality team involved. Are they driving the request to have a value in the field, or are you arbitrarily saying you don't like NULL values in a field? If they are driving it, then they should issue a change request to the vendor. If you are driving the need, then I'm thinking they may not like emails or anything coming to end users about the value. If NULL's are acceptable to them as the application owner, I'm not sure you should mess with it at all. (just my opinion)

 

[memcom]

a default value would not work because there are a number of possible options and there is not one that we can use as a default.

I am the one that decided a field needs to be populated and the software vendor usually does not create custom solutions like this for a reasonable fee. I tend to agree that messing with the software is not a good option, but the software has many short commings, so I have been forced to create a number of triggers to get the end results that I need to run my business properly.