August 28, 2012 at 2:21 am
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 ?
August 28, 2012 at 7:16 pm
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 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
August 29, 2012 at 9:55 am
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