December 6, 2003 at 3:04 am
hello
i am working in an application that needs logging at every stage
now i am trying to write a stored procedure which inserts records into three tables
i want to update a log table before and after every insert.
--log insert
--table1 insert
--log insert
--log insert
--table2 insert
--log insert
--log insert
--table3 insert
--log insert
the normal insert records need to be in a transaction
the problem is when i rollback the log records are also rolled back
i want to rollback only the records inserted to data tables
and keep the records in the log table
how can i achieve it?
pls help
Thanks
shiva
December 6, 2003 at 6:02 am
It seems you have to place 3 different actions in 3 different transactions where actions are pre-insert loggin, inserting and after-insert logging.
Or you can use savepoints in transactions, but it depends on type of errors that may appear. For example, constraint violition is possible that won't be a proper solution.
123123
123123
December 7, 2003 at 10:43 pm
Agree with previous post. Also presume you want to write a different message to the log if the main transaction fails? Perhaps it would be better to have one log record per insert, with a success/failure status dependent on whether the main transaction was committed?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2003 at 6:46 am
If you can put the "insert to log before insert to table" out of the transaction then begin transaction
insert into table
insert into log
commit transaction.
December 8, 2003 at 1:48 pm
Edwin is correct. You need the "insert into log" statement to be in a different transaction for it to committ regardless of a rollback for the remaining inserts.
-Isaiah
December 8, 2003 at 2:45 pm
Something like this may work
log insert - beginning sp
begin tran
--table1 insert
if errored
rollback
goto err1
--table2 insert
if errored
rollback
goto err2
--table3 insert
if errored
rollback
goto err3
commit
Finish:
return 0
Err1:
log insert - failed on insert a
return -1
Err2:
log insert - failed on insert b
return -2
Err3:
log insert - failed on insert c
return -3
December 9, 2003 at 3:33 am
the solution which cmore give above is wonderful ... but I have a question
what If I put Triggers on the 3 tables on insert and deletes ??? ... does it also solve the problem ... by writing when inserting .. and when rollback happened ... it will write (case of delete) ?
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply