May 16, 2008 at 12:01 pm
The SP below will popup an error message when the users of the app leave the "Freight Code" field blank. They can go back, fill that info in and click SAVE again.
My problem is that they can only try to correct once. For example:
-working on order, leave field blank, hit SAVE
-error pops up, order remains on screen unsaved and SAVE button is available
-leave field blank again, hit HIT
-error pops up, order remains on screen unsaved but SAVE button is grayed out
-user must close out of screen, loosing all unsaved work
For some reason I only get one chance to make the correction before hitting the SAVE button again. This is my first experience with RAISERROR and I'm not sure if this is due to the "severity" and "state" of the syntax.
Any suggestions would be greatly appreciated!
CREATE TRIGGER mandatory_freight_code_field ON oe_hdr
FOR INSERT
AS
DECLARE @FreightCode as varchar(1)
DECLARE @SourceCode as varchar(4)
select @FreightCode = freight_code_uid from inserted
--1 BOTH
--2 ALLOWED
--3 BILL-BOTH
--4 BILL-OUT
--5 BILL-IN
--6 COLLECT
--7 PREPAID
IF @@ROWCOUNT = 0
RETURN
IF @FreightCode in (1,2,3,4,5,6,7)
RETURN
IF @FreightCode = '' OR @FreightCode is null
BEGIN
RAISERROR ('*********************YOU MUST ENTER A DEFAULT FREIGHT CODE************************ *************************GO TO THE SHIP-INFO TAB TO CORRECT*************************** *************************************************************************************************',16,1)
END
May 16, 2008 at 12:40 pm
There is a bunch of small problems with this trigger.
1. This is an INSERT trigger so it does not fire on updates.
2. It does not cover all scenarios, for example if the value in FreightCode = 8 it does not do what it supposed to do.
3. You have unused variable @SourceCode.
4. You declared @FrightCode as Varchar(1) but in your code you compare it to a list of integers. Not a good thing to use implicit data type conversions.
But it looks like you may have some problems on the GUI end...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 16, 2008 at 12:56 pm
Thanks for replying -here is what I know so far:
1 - I don't care about updates, they need to have this code in place when they save for the first time. There will not be a situation where a freight code would ever be manually removed from an order after it's been saved. It might be changed, but not removed.
2 - Not a concern for us. These are just the "Freight_code_UID" and the GUI actually has a management tool to create these freight codes. The user won't enter a number (aka the freight_code_uid) he will enter the code itself (aka "BILL-OUT"). So, if he were to enter "8" for example, the GUI would toss out a "not a valid freight code" message.
3 - yeah, I forgot to take that out when I hacked up the original code, it's since been removed
4 - Seems to be working as-is so I'm not sure what the work around for this would be.
May 16, 2008 at 1:02 pm
If the error pops up in the UI but the button gets greyed out then you have to check the GUI code. Some of the event handlers are messed up.
Re 1. if you create a trigger to check if teh data is valid then you should check on updates as well.
Re 2. if the UI validates the data what do you need the trigger in the first place?
Re 3. no big deal, just my habit for a clean code....
Re 4. good coding practice - it works now but you should avoid implicit conversions because sooner or later you going to pay for it....
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 16, 2008 at 1:11 pm
Re 2 - the freight code isn't a mandatory field in the app and the users are forgetting to fill it out - causes major headaches down the road. Really stupid programming on the part of the developer, I want to force them to fill it out.
Re 1,3,4 - I see your points, I'm still a total newb to TSQL so I'm learning as I go.
Bummed to learn that the save button issue is a problem with the apps event handling. Getting them to address that will be almost as fun as pulling teeth.
Thanks for your help!
May 19, 2008 at 6:35 am
Your trigger will also not work correctly if more than one record is inserted at the same time - triggers in MS SQL are set based, the INSERTED table can have multiple records.
As an aside, you are just trying to force the values in the field to be 1-7.
A check constraint would be an easier, faster, and safer approach.
[font="Courier New"]ALTER TABLE dbo.oe_hdr ADD CONSTRAINT
Validate_Freight_Code CHECK (freight_code_uid >= 1 AND freight_code_uid <=7)[/font]
May 19, 2008 at 9:00 am
I tried that, also tried the variation below and it still allows me to save the order with a null field
ALTER TABLE dbo.oe_hdr ADD CONSTRAINT
Validate_Freight_Code CHECK (freight_code_uid >0 AND freight_code_uid <8)
Then I tried to experiement with this one (after removing all null values first) , to prevent null values:
ALTER TABLE [dbo].[oe_hdr] ALTER COLUMN freight_code_uid int NOT NULL
and it gives this message:
Msg 5074, Level 16, State 1, Line 1
The index 'idx_oe_hdr_shipping' is dependent on column 'freight_code_uid'.
Msg 5074, Level 16, State 1, Line 1
The index 'idx_oe_hdr_pt_scan_2' is dependent on column 'freight_code_uid'.
Msg 5074, Level 16, State 1, Line 1
The statistics 'freight_code_uid' is dependent on column 'freight_code_uid'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN freight_code_uid failed because one or more objects access this column.
May 19, 2008 at 9:06 am
You have everything right - add the constraint and the NOT NULL and you are in good shape.
The errors are simply that you have some index on the table that include that column. You need to drop those indexes before altering the column to not allow NULL. Then, just add them back to the table when done.
May 19, 2008 at 9:48 am
Good news is that you are right, using those steps I was able to get the constraints in place and they are working correctly on the dbase side.
However the GUI side of things is still a headache - with the constraints in place the app itself is tossing out an error message that the users will have trouble following, but that's something I need to take up with the app developers.
May 19, 2008 at 12:41 pm
I usually try to make the name of the constraint really descriptive. The default raised error from SQL contains the constraint name.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply