• 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