exception handling on triggers

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

Transact-SQL (2000)

exception handling on triggers

Author

Topic

nano
Starting Member

3 Posts

Posted - 2005-03-03 : 05:06:03

Need some help. How do I trap error in triggers so that I can show customized error. Or send me mail if error occurs. Code is like this:

CREATE TRIGGER testTRIGGER ON TABLE1
FOR INSERT, UPDATE
AS

BEGIN TRANSACTION

TRUNCATE TABLE2
INSERT INTO TABLE2
SELECT * FROM TABLE1

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Update Error', 16,1)
END
ELSE
COMMIT TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-03 : 09:59:25

You have more problems then worrying about error handling...

Is this something you're really trying to do?

Brett

8-)

TimS
Posting Yak Master

198 Posts

Posted - 2005-03-03 : 12:04:02

Note:

TRUNCATE TABLE2 -- will fail for normal users
SELECT * -- Select * should NOT be used in production code.
Since your are not using the inserted and deleted special tables most likely your code does not belong in a trigger.

Tim S

From BOL
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

nano
Starting Member

3 Posts

Posted - 2005-03-03 : 20:08:17

This one is similar to the actual code. The code works fine and it does update the second table. But when we try to change the table structure to create an error, it doesn't send us an email and just raises a system error and somehow stops without reaching the 'if @@error <> 0' part of the code, thus, unable to send us alert through mail. Can anyone help us on this.

IF EXISTS (SELECT name FROM dbo.sysobjects WHERE name = 'DataFeed' AND type = 'TR')
DROP TRIGGER dbo.DataFeed
GO

CREATE TRIGGER dbo.DataFeed ON dbo.ACSTI_TBL
FOR INSERT, UPDATE, DELETE
AS

BEGIN

BEGIN TRANSACTION

TRUNCATE TABLE dbo.ACVCM_TBL
INSERT INTO dbo.ACVCM (itemKey,
CompetencyGroupDescription,
GeographicUnitDescription,
MetroCityDescription,
.....n
)
SELECT itemKey,
CapabilityDescr,
GeographicUnitDescr,
MetroCityDescr,
......n
FROM dbo.ACSTI

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
EXEC sp_send_cdosysmail 'eric@hotmail.com',
'josh@yahoo.com',
'Update fail!'
'There was an error during update...."
RETURN
END
ELSE
COMMIT TRANSACTION

END
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 20:11:50

A lot of errors in triggers will abort the execution and here's nothing you can do about it.
This is not the sort of code you should consider putting in a trigger.

==========================================
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.

nano
Starting Member

3 Posts

Posted - 2005-03-07 : 04:08:10

So, there's no way to trap errors in triggers?

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-07 : 12:24:04

Nano,
What nr (And everyone else) is telling you:
This is not the type of code you should put in a trigger. It is poor design, to say the least. Just because it works, doesn't mean it is good, and you should really rethink what it is you are trying to do.
As for trapping errors in a trigger, they can be defined just the same as in a procedure in most cases...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources