Triggers or User Interface which way is better to Handle ERRORS in sql server

[eranfox]

Hello Everybody,
Can someone tell me which solution is better to use:
I want to trap all kinds of errors and send my own messages to
the user and i know of two ways of doing that:
1.check all the constraints in the user application (vb)
2.use triggers
well i know to use the first one but dont know how to use triggers
please if anyone knows (but realy knows) how to do this
or which way is better please e-mail me at [email protected]
or just post reply so all the others will know the answer.
THANK YOU IN ADVANCE
ERAN

[paulw]

You can't always use triggers for error handling because error conditions mean that the triggers do not fire. If an update does not happen due to an error then the OnUpdate trigger never occurs. Triggers are not a good way of handling errors.

The best way is to raise error states through your Stored Procedures and pass them on to the front-end application.

Cheers,

P.

[eranfox]

Hello paulw,
Thank you for your reply,
Now after your answer i still have two ways:
1.check for errors from the user application for e.g:
if len(text1.txt)>10 then msg.....
2.stored procedures
i heard its not so good to use stored procedures because they cant catch all the errors is that right?
Thank you again,
ERAN

[idemkovitch]

In VB you can do following:
Use combobox for preset vaues,
If field limited in size just set maxlength property to number of charachters.

If it's numeric use maskedit conTrol.
If it's date use MSDataPicker control.

An last, write

Function IsValidData() As Boolean
....
End Function

where you check if data for all other(not handled by controls)
valid.

Your update statement will be fired like this:
If IsValidData then Recordset.Update

[eranfox]

Hello idemkovitch,
Thank you for your reply,
but what if i need to enter employee birth date and employee starting date if the employees first day at work will be smaller then his birth date it will cause a problem
and still its look like i need to check everything from the client side
Thanks anyway,
ERAN

[Gary.Lowe]

You can use the insert trigger to validate data before it is entered into the table and also create a custom message in sysmessages if the validation fails.

The following is a trigger I have in my SQL Database to check wether the discount is between 0 & 100. If it isn't it calls the cutom message I created in sysmessages.

CREATE TRIGGER tblTillTransactionHeader_ITrig ON dbo.tblTillTransactionHeader
FOR INSERT
AS
/*
 * VALIDATION RULE FOR FIELD'TTrnHdrDiscount'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT (TTrnHdrDiscount Between 0 and 100)) > 0
    BEGIN
        RAISERROR(900017, 16, 1)
        ROLLBACK TRANSACTION
    END

The following is some more examples

CREATE TRIGGER tblTillTransactionDetail_ITrig ON dbo.tblTillTransactionDetail
FOR INSERT
AS
/*
 * VALIDATION RULE FOR FIELD'TTrnDetType'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT (TTrnDetType BETWEEN 1 AND 4)) > 0
    BEGIN
        RAISERROR(900029, 16, 1)
        ROLLBACK TRANSACTION
    END
/*
 * VALIDATION RULE FOR FIELD 'TTrnDetQuantity'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT(TTrnDetQuantity>=0 )) > 0
    BEGIN
        RAISERROR(900016, 16, 1)
        ROLLBACK TRANSACTION
    END
/*
 * VALIDATION RULE FOR FIELD 'TTrnDetAmount'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT(TTrnDetAmount>=0 )) > 0
    BEGIN
        RAISERROR(900018, 16, 1)
        ROLLBACK TRANSACTION
    END
/*
 * PREVENT INSERTS IF NO MATCHING KEY IN 'tbTillTransactionHeader'
 */
IF (SELECT COUNT(*) FROM inserted) !=
   (SELECT COUNT(*) FROM tblTillTransactionHeader, inserted WHERE (tblTillTransactionHeader.TTrnHdrTranNo = inserted.TTrnDetHeaderNo))
    BEGIN
        RAISERROR(900035, 16, 1)
        ROLLBACK TRANSACTION
    END

[paulw]

As with my previous reply, it depends upon the 'error'. Some of what you are doing is data validation, which is best handled in the front-end application. This is not an error as such. Triggers can be used (as Gary has shown) to apply validation rules but you have to be sure they will fire.

Stored procedures can always capture the error environment and you can be fairly creative but you would only go to the trouble of creating an SP to encapsulate work that is going to be repeated.

the best approach is to use all three. It really depends on what you are doing. If it is simply user input validation then I would do that in the front-end.

Cheers,

P.

[eranfox]

Thank you all for your replies,
ERAN


NO MORE WARS,NO MORE BLOODSHED!!!