March 11, 2011 at 8:40 am
in simple rollback tran trigger , it is giving this error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
however we want custom message..
TRIGGER [dbo].[TriggTest]
ON [dbo].[Table1]
FOR INSERT
AS
BEGIN
IF EXISTS ( SELECT * FROM INSERTED WHERE COLumn1 > 100)
ROLLBACK TRAN
PRINT ' INSERTS NOT ALLOWED FOR > 100 '
END
March 11, 2011 at 9:42 am
First, you need a Begin and End for the If, otherwise it just controls the next command.
Second, use Raiserror instead of Print.
Third, why not do this in the insert proc, instead of in a trigger? You'll have better control over the whole process that way. Put a check constraint on the table to hard-prevent out-of-range values, and then capture and control the error in the insert proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2011 at 11:52 am
the application working for 1-2 years and now they don't want to change sp or tested code.
sometimes users directly inserts records in the table. check constraints not an option as table already contains old data of out of range number , we want old data too and in future inserts are not allowed.
using begin and end , i m still receving std trigger error , we want customize error that inserts all not allowed.
March 11, 2011 at 12:40 pm
As GSquared said use raise error.
CREATE TABLE table1 (column1 int)
GO
CREATE TRIGGER [dbo].[TriggTest]
ON [dbo].[Table1]
FOR INSERT
AS
BEGIN
IF EXISTS ( SELECT * FROM INSERTED WHERE COLumn1 > 100)
BEGIN
ROLLBACK TRAN
RAISERROR (' INSERTS NOT ALLOWED FOR > 100 ', 10, 1)
END
END
GO
INSERT INTO Table1 VALUES (101)
go
You get a result of :
INSERTS NOT ALLOWED FOR > 100
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Someone else who knows more about using raiserror (I'm not terribly good at it myself) can probably tell you if its possible to get rid of the "The transaction ended in the trigger. The batch has been aborted." part.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 11, 2011 at 2:32 pm
To hide that error, you need to either add Try Catch blocks to the T-SQL code doing the insert, or you need to have the front end application capture the error. Either one will require rewriting code outside the trigger.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply