Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Suppressinig error values Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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
Posted Tuesday, August 28, 2012 7:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 PM
Points: 3,627, Visits: 5,273
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1351360
Posted Wednesday, August 29, 2012 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:25 PM
Points: 191, Visits: 895
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 »

Add to briefcase

Permissions Expand / Collapse