March 31, 2011 at 5:33 am
I have an application that has a notes field. I have changed the notes field at the table level to not allow nulls.
When a null is entered, The application simply takes the sql error message and returns it to the user.
I was wandering is there any way of capturing this message and returning something more meaning full to the user?
I have no access to the application source code.
Is this possible?
Cheers...Steve
March 31, 2011 at 5:40 am
If the application calls a Stored Procedure that you do have access to, then yes, you can wrap it in a TRY...CATCH block and raise a custom error message.
If it just runs ad-hoc SQL from within the application, the only way you could do so would be to use a trigger instead of insert that tests for NULL and raises an error if it is so - note that you'd probably have to remove the NOT NULL constraint and enforce it purely through the trigger as the constraint violation can be raised before the trigger is fired.
March 31, 2011 at 7:26 am
Thanks Howard.
I have located the stored procedure and will give the TRY....Catch a whirl.
Cheers...Steve
April 1, 2011 at 11:35 am
HowardW (3/31/2011)
If the application calls a Stored Procedure that you do have access to, then yes, you can wrap it in a TRY...CATCH block and raise a custom error message.If it just runs ad-hoc SQL from within the application, the only way you could do so would be to use a trigger instead of insert that tests for NULL and raises an error if it is so - note that you'd probably have to remove the NOT NULL constraint and enforce it purely through the trigger as the constraint violation can be raised before the trigger is fired.
Regarding the emphasized portion of your comment above: your statement is correct with respect to AFTER triggers, however I am not finding this to be true of INSTEAD OF triggers. If I missed the intent of your comment I apologize, please clarify.
Here is a code sample:
USE test
GO
CREATE TABLE dbo.fake_table
(
id INT,
notes VARCHAR(100) NOT NULL
) ;
GO
CREATE TRIGGER dbo.fake_table_ii ON dbo.fake_table
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.fake_table
(
id,
notes
)
SELECT id,
ISNULL(notes, 'no note')
FROM inserted
END
GO
INSERT INTO dbo.fake_table
(id, notes)
VALUES (1, 'some notes...'),
(2, NULL)
GO
SELECT *
FROM dbo.fake_table
GO
DROP TABLE dbo.fake_table
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2011 at 2:01 am
opc.three (4/1/2011)
Regarding the emphasized portion of your comment above: your statement is correct with respect to AFTER triggers, however I am not finding this to be true of INSTEAD OF triggers. If I missed the intent of your comment I apologize, please clarify.
I may have got that wrong - BOL does mention that the action of an INSTEAD OF trigger is rolled back if a constraint violation occurs, but also clearly states that it fires before constraint checks - I seem to remember a scenario that you couldn't use INSTEAD OF triggers to prevent the constraint violation from occuring, but can't find reference to it now, so I guess this is something I need to unlearn 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply