471,853 Members | 814 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

Stored procedure rollback

Hey everyone,

I have a set of stored procedures that call each other to perform a
set of tasks. I have decided to do this for database performance
reasons rather than doing it from a ColdFusion page (and to prevent
timeouts, etc.)

These stored procedures run every night and handle scheduled tasks in
a complex system.

An example of this would be:
sp_ExecuteScheduledTasks @ForDate
(calls) sp_UpdateEmployeeSalary @EmployeeID, @NewSalary,
@EffectiveDate
(then calls) sp_UpdateEmployeeCoverage @EmployeeID,
@PlanEnrollmentYear
(calls) sp_UpdateEmployeeBenefitCoverageandCosts @EmployeeID,
@PlanEnrollmentYear

This is a quickly drafted example of the complexity of this stored
proc routine.

The problem I have is that each time an error occurs in this stored
proc routine, I would like to save it to a table in the database for
future analysis and rectification the following day. The reason this
does not happen is because of the nested transactions. If the
transaction is going to fail, it also rolls back transactions within
itself (it's children transactions).

I've put together a very basic set of SQL statements below in order to
test and try to find a solution to my problem but have not yet
succeeded.

/**************************/
/* SQL Example Start */
/**************************/

BEGIN TRAN tran_with_error

INSERT INTO tblThisCausesAnError(error) VALUES('This causes an error')

BEGIN TRAN save_error_message
INSERT INTO tblErrorLog(message) VALUES ('This reports an error')

COMMIT TRAN save_error_message
/* SAVE TRAN save_error_message */

/* At this point an error has occured, rollback the transaction */
ROLLBACK TRAN tran_with_error

/**************************/
/* SQL Example End */
/**************************/

I have tried committing/saving the transaction, I have tried
disallowing delete access to the tblErrorLog table to public and the
SQL user that executes the process, I have even tried a delete trigger
that disallows any deletion but I realize that transactions are
handled differently and that a rollback is not considered a deletion.

Any ideas?

Thanks.

Brent
Jul 20 '05 #1
0 6785

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by C Kirby | last post: by
9 posts views Thread by dtwilliams | last post: by
3 posts views Thread by Mark | last post: by
1 post views Thread by Belee | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.