Need Help on Error Handling in Trigger..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help on Error Handling in Trigger..

Author

Topic

vdpramesh
Starting Member

12 Posts

Posted - 2003-08-27 : 05:18:53

I am using the following Trigger on my appln.Here my problem is ,whenever i get any error (including contraints,null,any error) should be captured and displayed for my customized message.
Pls help me on this...Is this right ?

CREATE TRIGGER test_trigger ON dbo.testmydata
after INSERT
AS
DECLARE @CONFIRM INT
begin
if (@@ERROR <> 0) goto ErrorHandler
update qry....
if (@@ERROR <> 0) goto ErrorHandler
BEGIN
EXEC @CONFIRM=master.dbo. xp_sendmail @recipients 'sd@dhd.com',@subject ='Premises Alert Message',@message = 'sdsd'
END
if (@@ERROR <> 0) goto ErrorHandler
ErrorHandler:
RAISERROR ('An error occured while executing the trigger.',16,2)
end

This is the Generating two message -one is from system and another is my message.I dont want to display the system message.any error occurs mesage should be displayed my custom message.
-Thanx

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-27 : 08:38:20

I'm afraid to insist on that but it's impossible.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-27 : 09:07:48

In sql server there are a lot of errors that are untrappable. Constraint violations in a trigger is currently one of thgem.
If you try a simple test you will see that the statement following the error is not executed.

You might also want to do a rollback in the trigger error handler if the calling code doesn't do this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources