Post reply

DTS and Triggers

  • April 7, 2004 at 5:53 pm

    #59252

    Hi All

    I am loading some data to a table using a DTS.  This table has Insert/Delete/Update triggers for capturing the data and changes for the purpose of audit/history to a history table.

    However when the data is loaded via DTS the insert triggers do not fire.  If youload the data manually they work.  Has anyone encountered this problem and if so could you please let me know how to work around this issue as manual data load is not an option due to the volume of data.

    Thanks

    Dinesh

     

  • April 8, 2004 at 1:50 am

    #502090

    Dinesh,

    Have your triggers got cursors in them to process the multiple records in the inserted/deleted tables?

    If not, the following should help, had to convert my triggers for mass updates via DTS calls...

    -- create a cursor to select the unique id from inserted
declare installations_cursor cursor FAST_FORWARD READ_ONLY for 
  select InstallId from inserted where Status <> 2

open installations_cursor
fetch next from installations_cursor into @@InstallId
while @@FETCH_STATUS = 0
begin
    /* process the records where unique id in table = @@InstallId */

-- loop the cursor
fetch next from installations_cursor into @@InstallId
end
 
close installations_cursor
deallocate installations_cursor

    Hope this helps,

    JustinB

  • April 8, 2004 at 2:47 am

    #502099

    You need to turn off the fast load option in DTS.

    I haven't got it in front of me at the moment but i think it should

    be in one of tabs when you right click the transformation and look at the properties of it.

    I had exactly the same problem took me a few hours to work it out.

    Regards

    Dave

  • April 8, 2004 at 4:10 am

    #502105

    With a DTS, is a cursor-based trigger necessary?  Cursors are very slow, so wouldn't a set-based trigger work better?

  • April 8, 2004 at 5:13 am

    #502109

    I agree with Glenda. Cursor-based triggers are a relic and should be replaced wherever possible ie. in 99.99% of cases.

  • April 8, 2004 at 10:34 am

    #502183

    Turning off the fast load option on your DTS transformation will allow the trigger to run.  Also be sure as mention above that your trigger will work when multiple inserts are done in a single statement. 

    I would avoid cursors if at all possible.  Using the inserted table should let you do all the work you need in the trigger using set basid approach.

  • April 9, 2004 at 6:57 am

    #502281

    I agree that cursors should be avoided at almost all costs.  If you find this is one of those rare events that would require a record iteration, however, since you are using a DTS package, you could use an Active X Script task to iterate through the records using an ADO recordset to accomplish the task.

  • April 12, 2004 at 6:11 pm

    #502484

    Hi All

    Thanks for your responces, the fast load option was turned off and the inserts were captured to the history table sucessfully.

    Thanks very much

    Dinesh

     

     

  • January 7, 2005 at 8:05 am

    #536429

    it solved our problem... gr8

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply