SQL Server Performance Forum – Threads Archive
trying to st up log for process using a trigger.
I have a problem where a third party written group of procedures are doing a rollback for some reason, and wipping out the records that were inserted into a table. What I need to do is to record anything that is ever inserted into this payment table, using a trigger, I set up a on insert trigger to copy the record into a log table. But when the rollback occurs in the procedure it rolls back all transactions that have started with that procedure. including the insert trigger into my log table. How can I stop this rollback on the log table from ocurring? or is there an easier way to accomplish a table log of records being inserted.Thanks for your help.
wolffy
Any way to post trigger and sp?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I know this sounds weird but exporting the table records into text file in the middle of transaction may help… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
gaurav,
No that does not sound weird, and I thought about it. but I will need to write a page that will do a search on this log table. And I should not have to resort to text documents to work around. Thanks for the suggestion. What else do you have for me?
I will write to a text file until someone can find a solution for me, but it will take a lot of work for me to search on it.
This must have come up before.
Thanks for your help.
Wolffy
What if you start a job from the trigger on Insert on the table. The job will essentially write the required records to new log table. As far as I know this is something which can’t be rolled back by transaction. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Gaurav,
I am not sure I know what you mean. I have the trigger on the payment table that I want to log, a stored procedure inserts into this payment table, which started the trigger and inserts the record into the log table.
Now everything I have read and experienced with this shows that if a procedure calls an insert into a table and rolls back , all processes associated with that insert are rolled back including the trigger. Is that what you are talking about? Thanks,
Wolffy
I said create a job which will be called sp_start_job from the trigger. The job will simply get the inserted data from the table and insert it into saperate table. Now since job’s execution is not a part of trigger execution, even if the transaction is rolled back, the data in log table will still remain. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
gaurav,
Great Idea, I will try that. Wolffy.
qaurav,
before trying to use a job and since I don’t see how I will deliver parameters to a job, I wanted to try using a trigger to write the record to a file. which works fine, but is over write the file everytime. I have searched whith no success, for a way to append to a file. I am using "bcp queryout". Do you know of a flag that will append. or is this a file write only process? is there another way to append to a file?
I thought there was, but can not remember.
Thanks,
Wolffy
Wolffy are you just trying to trouble shoot the behavoir or are you looking for something longer term? If you are only trying to track down the conditions of the rollback, then for a short term, try profiler/trace. Store the information and then you can look at the information to see what is happening. One thing I can think that would still keep wtih your preference of using the trigger is make the output file name ‘paramaterized’ based on the date/time. This would get you one file per tigger through, not an ideal situation. Or within the trigger, you can do the following
a)bcp queryout the changes (overwriting any file)
b)use command line statements to append the contents of the new file to another file There has to be an easier way. Just not sure right now.
Everyone, I think I have found a solution, I am working on it now. I found a procedure that will append to a file, I will call this procedure from a trigger, stringing the inserted.values together. I will let you know if it works. Thanks,
Wolffy
Don’t forget to tell us what the procedure was…. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
I’m with Chris on this one. Run a trace and try to figure out what is causing the rollback. If you need to backup the data for now, bcp to a file is quite a simple way. But I would concentrate on trying to find the cause for the rollback.
I tried the trace when we first started experiencing the problem. but could not catch the roll back issue, it happens very infrequently, maybe once or twice a month. I will set up a trace again after I get this procedure append_to_file working, which is almost done. I am not sure what parameters to set up for the trace, don’t want to save all that data since it may take a month for this to show up again. Thanks for your help,
Wolffy
Ok, I have set up a trigger that calls a stored procedure which appends to a file, here the the stored procedure I found onhttp://www.pstruh.cz/tips/detpg_SQLWrFile.htm
——————————————————————–start
CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate ‘Scripting.FileSystemObject’, @FS OUT
IF @OLEResult <> 0 PRINT ‘Scripting.FileSystemObject’ –Open a file
execute @OLEResult = sp_OAMethod @FS, ‘OpenTextFile’, @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT ‘OpenTextFile’ –Write Text1
execute @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @Text1
IF @OLEResult <> 0 PRINT ‘WriteLine’ EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
———————————————————————–end the trigger just adds the interted field values together and sends them to AppendToFile. works pretty good so far, Now when we get a complaint that a payment is missing I will be able to prove that it did exsist which it must have since the end user has the unique id number for the record. I am hoping that it this will show that the data being entered is different in some way. if not don’t have a clue what my next step will be. Tracing is still an option, but don’t know what to set up for long term use, this may have to run a month before the same error occurs.
Thank you for any help or insights you can give me for this issue. Wolffy.
]]>