Logging Trigger Errors - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Logging Trigger Errors

Author

Topic

MrT36
Starting Member

6 Posts

Posted - 2007-07-09 : 14:59:33

Is there a way to force a trigger to completly fire in order to capture the @@error message, or is there something else I may be doing wrong here?

Example trigger:
insert into TestTable
(
FIELDA,
FIELDB,
FIELDC
)
SELECT
convert(varchar(255),del.field_a),
convert(varchar(255),del.field_b),
convert(varchar(255),del.field_c)
FROM deleted del
IF @@error != 0
BEGIN
SET @savedError = @@error
INSERT into logTable
(
datetime,
desc
)
VALUES
(
convert(char(20), getdate(), 13),
'Error inserting into TestTable'
)
END

I tested this by removing my TestTable and then making an an update to the table with the trigger. I can always get a message in a result window, but never get into my error check in the trigger for logging to the logTable.

Thanks,

MrT36

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-09 : 15:16:24

@@error holds the error info of the last executed line.
in you SET @savedError = @@error the @@error is 0

do
SET @savedError = @@error
if @savedError <> 0
....

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

MrT36
Starting Member

6 Posts

Posted - 2007-07-09 : 15:52:24

Thanks for that correction.

I still don't get into the error handling code. When using Enterprise Manager, I get a window notifying me of the error, but does not allow the trigger to complete. Could it be the way I am trying to update the table which contains the trigger (i.e. Enterprise Manager and or SQL Query Analyzer)?

MrT36

MrT36
Starting Member

6 Posts

Posted - 2007-07-13 : 17:29:09

This still does not allow the error code to complete. Anyone have an idea how to force a trigger to complete when an error is encountered?

MrT36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources