Suppressinig error values

  • I am using a trigger to check some business logic before doing an insert. If the business logic fails, it should return a custom message and rollback the insert operation.

    After implementation, I checked and found that it is returning the custom message but giving the system error message for rolling back the transaction like The transaction ended in the trigger and the batch has been aborted. Is it possible to only show the custom message and not system error message ?

  • I believe that the answer is no.

    Business logic should be applied prior to the data being INSERTed. Triggers are more for stuff like:

    - Suppressing updates to certain columns

    - Creating an audit trail on changed data

    - INSERTing, UPDATEing or DELETEing to a secondary table when certain information comes in.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I agree. Checks should be done before you even get to the insert or update stage. If you are using SPROCs to do the insert or update, the logic should be there to do the checks first.

    Here is some generic error logging code that I use. I use a Traces database as my DBA info database, one per instance. This holds Profiler trace information, general error messages (as used below), etc. Below that is a sample trigger with error trapping (writing to an AppErrorLog table in the database where the error occured).

    /* ================================================================================================================ */

    /* Code for error messages */

    /* ================================================================================================================ */

    DECLARE

    @ErrorMessageNVARCHAR(4000),

    @ErrorNumberINT,

    @ErrorSeverityINT,

    @ErrorStateINT,

    @ErrorLineINT,

    @ErrorProcedureNVARCHAR(200)

    IF OBJECT_ID(N'Traces.dbo.AppErrorLog', N'U') IS NULL

    BEGIN

    CREATE TABLE Traces.dbo.AppErrorLog

    (EID int IDENTITY NOT NULL

    CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,

    EDatedatetimeNOT NULL,

    EUservarchar(50)NULL,

    EMessagevarchar(1000)NULL,

    ESource varchar(150)NOT NULL,

    ENointNOT NULLDEFAULT 0,

    ESeverityintNOT NULLDEFAULT 0,

    ELineNo intNOT NULLDEFAULT 0,

    EHost varchar(50)NULL

    )

    END

    BEGIN TRY

    --do something here

    END TRY

    BEGIN CATCH

    SELECT

    @ErrorMessage= ERROR_MESSAGE(),

    @ErrorNumber= ERROR_NUMBER(),

    @ErrorSeverity= ERROR_SEVERITY(),

    @ErrorState= ERROR_STATE(),

    @ErrorLine= ERROR_LINE(),

    @ErrorProcedure= ISNULL(ERROR_PROCEDURE(), '-');

    --RAISERROR

    --(

    --@ErrorMessage,

    --@ErrorSeverity,

    --1,

    --@ErrorNumber, -- parameter: original error number.

    --@ErrorSeverity, -- parameter: original error severity.

    --@ErrorState, -- parameter: original error state.

    --@ErrorProcedure, -- parameter: original error procedure name.

    --@ErrorLine -- parameter: original error line number.

    --);

    -- Put a record in AppErrorLog to show the error message for review later

    INSERT INTO Traces.dbo.AppErrorLog

    VALUES (GETDATE(), USER, @ErrorMessage, '<description of where this is at and other values>', @ErrorNumber, @ErrorSeverity, @ErrorLine, HOST_NAME())

    PRINT 'ERROR - Found error when attempting to ???. Error=' + @ErrorMessage

    END CATCH

    /* ====================================================================================== */

    /* Create Trigger for table Account to handle Last Change and/or Audit */

    CREATE TRIGGER dbo.TG_Trigger_On_Table_Account

    ON dbo.Account

    FOR INSERT, UPDATE, DELETE

    AS

    BEGIN TRY

    DECLARE @idvalint

    DECLARE @CountDelint

    DECLARE @CountInint

    SELECT @CountDel = COUNT(*) FROM Deleted

    SELECT @CountIn = COUNT(*) FROM Inserted

    if @CountDel = 0 and @CountIn = 1-- inserted

    BEGIN

    BEGIN TRANSACTION

    UPDATE Account

    SET LCDATE = GETDATE(),

    LCUSER = USER,

    LCHOST = HOST_NAME()

    FROM dbo.Account s

    JOIN inserted i

    ON i.AcctID = s.AcctID

    COMMIT TRANSACTION

    END

    IF @CountDel = 1 and @CountIn = 0-- deleted

    BEGIN

    BEGIN TRANSACTION

    INSERT INTO AccountAudit

    SELECT * FROM DELETED

    INSERT INTO AccountAudit

    SELECT * FROM DELETED

    SET @idval = @@identity

    UPDATE AccountAudit

    SET FinalDelete=1,

    LCHost = HOST_NAME(),

    LCUser = USER,

    LCDate = GETDATE()

    WHERE AcctAuditID = @idval

    COMMIT TRANSACTION

    END

    if @CountDel = 1 and @CountIn = 1-- updated

    BEGIN

    BEGIN TRANSACTION

    INSERT INTO AccountAudit

    SELECT * FROM DELETED

    UPDATE Account

    SET LCDATE = GETDATE(),

    LCUSER = USER,

    LCHOST = HOST_NAME()

    FROM dbo.Account s

    JOIN inserted i

    ON i.AcctID = s.AcctID

    COMMIT TRANSACTION

    END

    END TRY

    BEGIN CATCH

    DECLARE@ErrorNoint,

    @Severityint,

    @Stateint,

    @LineNoint,

    @errmessagevarchar(1000)

    SELECT@ErrorNo = ERROR_NUMBER(),

    @Severity = ERROR_SEVERITY(),

    @State = ERROR_STATE(),

    @LineNo = ERROR_LINE(),

    @errmessage = ERROR_MESSAGE()

    ROLLBACK TRAN

    INSERT INTO AppErrorLog

    VALUES (GETDATE(), USER, @errmessage, 'TG_Trigger_On_Table_Account', @ErrorNo, @Severity, @LineNo, HOST_NAME())

    END CATCH

    GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply