How to use SQL triggers to log errors in a SQL table?

5 pts.
Tags:
SQL Server
SQL Server errors
SQL Server triggers
How to log errors in sql table using sql triggers

Answer Wiki

Thanks. We'll let you know when a new response is added.

You wouldn’t use triggers to log errors into the SQL Server. You would need to put code into the stored procedures that make up your application to log any errors which are encountered.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,185 pointsBadges:
    report
  • Kccrosser
    The primary "gotcha" with trying to record errors into a database table is that the error is normally followed by a rollback of the transaction, which then discards the error record(s), since they are being written implicitly as a part of the overall transaction. Oracle provides a very useful system package - dbms.alerts - that allows creating a nice solution to this problem. I am not a SQL Server guru, so I won't try to give the SQL Server equivalent, but will describe how the package is used. The Oracle Alerts package allows you to create a "listener" process, which is thus detached from the "sender" process, and then send/post an Alert from one process to the other. A "side effect" of Alerts is that once you post an alert, it is not affected by subsequent commits or rollbacks - it is like sending out an e-mail. Further, Alerts is an automatic FIFO queue, so we didn't have to worry about process timings. Thus, we created a simple background Oracle job (the "logger") that started up and "listened" for Alerts on a specific virtual pipe. When it received an alert, it wrote the contents into a database log table as a single record transaction and a commit, then went back to listen for another one. Minor technical note - we actually had a public package that the "senders" called, which generated some housekeeping stuff. The "sender" usually called with 4 parameters - the SQL error number (or 0 for non-error logs), the SQL error text (or null), the "type" of the log ("E" for error, "L" for general logs, "S" for status, "D" for debug, etc.), and the current "procedure name" (i.e., stored procedure or function name). The public shared package took those parameters and added a number of system information fields - the date/time of the record, the process id, the workstation/terminal name, the user information from the process, etc. Then, when the logger wrote the data, it also added a unique sequence number. Then, in all of our normal transactions, when there was something we wanted to record (particularly in the error handlers), we called the log package with the simple parameters, and it just copied the information into a simple message structure and posted the data as an Alert to the listener job. Voila - this made it trivial to instrument our transactions to record errors (and debug/status info) in SQL transactions without disrupting the actual transaction. Any SQL Server expert who knows of a similar function, please feel free to discuss how to implement a similar logger... I would be interested to know what corresponding facilities exist in SQL Server.
    3,830 pointsBadges:
    report
  • Denny Cherry
    In T/SQL we simply roll back the transaction then insert the logging data into the log table.
    BEGIN TRANSACTION
    BEGIN TRY
        Some statement which fails.
    END TRY
    BEGIN CATCH
    
    ROLLBACK
    
    INSERT INTO LogTable
    (ErrorMessage, ErrorNumber, ...)
    SELECT ERROR_MESSAGE(), ERROR_NUMBER(), ...
    
    END CATCH
    
    IF @@TRANCOUNT <> 0
    COMMIT
    66,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following