none
Insert a number of rows in to an audit table for each row in another table RRS feed

  • Question

  • I want a trigger that will insert a number of rows in to an audit table for each row in another table, should I use a trigger for this?

    Tuesday, August 30, 2011 1:14 PM

All replies

  • Yes, you can use a trigger.... a OUTPUT clause cannot insert multiple rows into the audit table.

    But use a after insert trigger.

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 30, 2011 1:19 PM
  • what do you mean by: a OUTPUT clause cannot insert multiple rows into the audit table
    Tuesday, August 30, 2011 1:20 PM
  • Generally speaking if this is a transactional system that is under heavy usage I'd advise against triggers, as the initial insert has to wait for the trigger to finish before returning, which depending on your isolation levels can cause all kinds of badness.

    If this is the case maybe look at the service broker instead of triggers 

    http://msdn.microsoft.com/en-us/library/bb522893.aspx

     

     

    Thanks

     

    Dave

    Tuesday, August 30, 2011 1:21 PM
  • Yes, you can use a trigger.... a OUTPUT clause cannot insert multiple rows into the audit table.

    But use a after insert trigger.

     


    Nothing is Permanent... even Knowledge....
    My Blog

    An OUTPUT statement can return multiple rows, simply pipe it into a table variable then use that for the insert, see here for an example 

     

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Tuesday, August 30, 2011 2:06 PM