By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,414 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,414 IT Pros & Developers. It's quick & easy.

Commit Transaction Even on Trigger Failure

P: 39
I am trying to pull data out of an application database and transform it to another medium.
I have direct access to the database, but I cannot alter the program code.

What I want to have happen is that when an insert occurs on a particular table, execute a statement that will put the data it needs into a string and export the file.

I have the code working with one small problem. If something goes wrong in my code (like a conversion error, or the file server is not available), it actually does a ROLLBACK TRANSACTION on the initial insert statement. This is bad because it causes a missing reference in the application database.

What I'd like to have happen is that even if my trigger fails, go ahead and insert the record.

Here's what I have so far:
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE trigger [trg_CIM_WO_Issue]
  3. on [dbo].[RunIDMapReportID]
  4. after insert
  5. as
  6. --delay for 1 second to ensure all ingredients finish inserting
  7. waitfor delay '0:0:1.000'
  8.  
  9. Declare @WO char(4), 
  10. @ComponentItemCode varchar(7), 
  11. @SubWeight decimal(15,5), 
  12. @Lot varchar(25), 
  13. @cimOut nvarchar(4000),
  14. @cnt int
  15.  
  16. --zero out counter
  17. Set @cnt = 0
  18.  
  19. --add beginning to work order issue cim file
  20. Set @cimOut = '@@batchload wowois.p'
  21.  
  22.  
  23. --Scroll through all the ingredients for the inserted ReportID and add them to the CIM output
  24. Declare cur_0 scroll cursor for
  25. Select C.RunID, B.ComponentItemCode, B.SubWeight, B.LotNumber
  26. From BatchReport A
  27. inner join BatchReportDetail B
  28. on A.ReportID = B.ReportID
  29. inner join RunIDMapReportID C
  30. on A.ReportID = C.ReportID
  31. Where A.BatchDone = 1 and B.ComponentItemCode like '2%' and B.Deduct = 1
  32. and B.ReportID = (Select ReportID From inserted)
  33. Order by B.ComponentItemCode
  34.  
  35. open cur_0
  36.  
  37. FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot
  38.  
  39. While @@Fetch_Status = 0
  40. Begin
  41. Set @cnt = @cnt +1
  42.  
  43. --first time through add the work order
  44. If @cnt = 1
  45.     Begin
  46.     Set @cimOut = @cimOut + '
  47. ' + @WO
  48.     End
  49. --each time add the ingredient issue lines
  50. Set @cimOut = @cimOut + '
  51. ' + @ComponentItemCode + '
  52. ' + convert(varchar(25),@SubWeight) + ' - - - - ' + @Lot
  53.  
  54.  
  55. FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot
  56. End
  57.  
  58. close cur_0
  59. deallocate cur_0
  60.  
  61. --Add end line to string
  62. Set @cimOut = @cimOut + '
  63. @@end'
  64.  
  65. --If we had any data to output, use OLE automation to put it into a timestamped file on the 
  66. --server and write out a history file with the WorkOrder, ReportID and the date it was processed
  67. If @cnt > 0
  68. Begin
  69.     --declare variables necessary for processing the CIM output into a file using OLE
  70.     DECLARE @FS int, @OLEResult int, @FileID int, @FileName varchar(255)
  71.  
  72.     --Generate a filename with a fixed length timestamp
  73.     Set @FileName = '\\servername\d$\CIM\wois_cim_' + convert(char(8),GETDATE(),112)+replace(convert(char(12),getdate(),114),':','') + '.cim'
  74.  
  75.     EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
  76.     IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
  77.  
  78.     --Open the file
  79.     execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
  80.     IF @OLEResult <> 0 PRINT 'OpenTextFile'
  81.  
  82.     --Write the Text
  83.     execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @cimOut
  84.     IF @OLEResult <> 0 PRINT 'WriteLine'
  85.  
  86.     --cleanup OLE objects
  87.     EXECUTE @OLEResult = sp_OADestroy @FileID
  88.     EXECUTE @OLEResult = sp_OADestroy @FS
  89.  
  90.     --write to history log
  91.     Insert tblCIMhist
  92.     Select RunID, ReportID, getdate()
  93.     From inserted
  94. End
  95.  
Nov 12 '07 #1
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
Not sure if it will help but you can try to catch an error:

Expand|Select|Wrap|Line Numbers
  1. EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
  2. If @@Error <> 0
  3.     PRINT ‘Error Scripting.FileSystemObject'
  4. ELSE
  5.     IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
Good Luck.
Nov 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.