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:
-
-
CREATE trigger [trg_CIM_WO_Issue]
-
on [dbo].[RunIDMapReportID]
-
after insert
-
as
-
--delay for 1 second to ensure all ingredients finish inserting
-
waitfor delay '0:0:1.000'
-
-
Declare @WO char(4),
-
@ComponentItemCode varchar(7),
-
@SubWeight decimal(15,5),
-
@Lot varchar(25),
-
@cimOut nvarchar(4000),
-
@cnt int
-
-
--zero out counter
-
Set @cnt = 0
-
-
--add beginning to work order issue cim file
-
Set @cimOut = '@@batchload wowois.p'
-
-
-
--Scroll through all the ingredients for the inserted ReportID and add them to the CIM output
-
Declare cur_0 scroll cursor for
-
Select C.RunID, B.ComponentItemCode, B.SubWeight, B.LotNumber
-
From BatchReport A
-
inner join BatchReportDetail B
-
on A.ReportID = B.ReportID
-
inner join RunIDMapReportID C
-
on A.ReportID = C.ReportID
-
Where A.BatchDone = 1 and B.ComponentItemCode like '2%' and B.Deduct = 1
-
and B.ReportID = (Select ReportID From inserted)
-
Order by B.ComponentItemCode
-
-
open cur_0
-
-
FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot
-
-
While @@Fetch_Status = 0
-
Begin
-
Set @cnt = @cnt +1
-
-
--first time through add the work order
-
If @cnt = 1
-
Begin
-
Set @cimOut = @cimOut + '
-
' + @WO
-
End
-
--each time add the ingredient issue lines
-
Set @cimOut = @cimOut + '
-
' + @ComponentItemCode + '
-
' + convert(varchar(25),@SubWeight) + ' - - - - ' + @Lot
-
-
-
FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot
-
End
-
-
close cur_0
-
deallocate cur_0
-
-
--Add end line to string
-
Set @cimOut = @cimOut + '
-
@@end'
-
-
--If we had any data to output, use OLE automation to put it into a timestamped file on the
-
--server and write out a history file with the WorkOrder, ReportID and the date it was processed
-
If @cnt > 0
-
Begin
-
--declare variables necessary for processing the CIM output into a file using OLE
-
DECLARE @FS int, @OLEResult int, @FileID int, @FileName varchar(255)
-
-
--Generate a filename with a fixed length timestamp
-
Set @FileName = '\\servername\d$\CIM\wois_cim_' + convert(char(8),GETDATE(),112)+replace(convert(char(12),getdate(),114),':','') + '.cim'
-
-
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
-
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
-
-
--Open the file
-
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
-
IF @OLEResult <> 0 PRINT 'OpenTextFile'
-
-
--Write the Text
-
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @cimOut
-
IF @OLEResult <> 0 PRINT 'WriteLine'
-
-
--cleanup OLE objects
-
EXECUTE @OLEResult = sp_OADestroy @FileID
-
EXECUTE @OLEResult = sp_OADestroy @FS
-
-
--write to history log
-
Insert tblCIMhist
-
Select RunID, ReportID, getdate()
-
From inserted
-
End
-
