Suppressinig error values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Suppressinig error values

Suppressinig error values

Rate Topic

Display Mode

Topic Options

Author

Message

sqlnaive

Posted Tuesday, August 28, 2012 2:21 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 2,661, Visits: 1,672

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 ?

Post #1350724

dwain.c

Posted Tuesday, August 28, 2012 7:16 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1351360

vikingDBA

Posted Wednesday, August 29, 2012 9:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189, Visits: 863

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
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(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,
EDate datetime NOT NULL,
EUser varchar(50) NULL,
EMessage varchar(1000) NULL,
ESource varchar(150) NOT NULL,
ENo int NOT NULL DEFAULT 0,
ESeverity int NOT NULL DEFAULT 0,
ELineNo int NOT NULL DEFAULT 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 @idval int
DECLARE @CountDel int
DECLARE @CountIn int

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 @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@errmessage varchar(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

Post #1351738

« Prev Topic | Next Topic »

Permissions