Triggers Per Row Not Per Statement

Hi guys



I am wondering if there is any way of setting the trigger to execute once per individual row that is updated as appose to once per statement. Thus a statement like this would trigger twice not just once:


Update Order

Set Name = 'test'

Where OrderId In (1,2)



If this is not possible do the Deleted and Inserted tables have the records for all the items that were updated in the statement?



The reason being that i need to store the before snapshot and after snapshot of the updated data in an audit database along with an event that ties the two audit records together. This event is a business logic event, which is stored in the main database and records which user made the change, what other records the change is related to and impacts, etc, etc. Thus after the above update is complete, I would have 4 audit records, 2 being pre shots and 2 being post shots, along with 2 event records. Where each event record ties one of the pairs of pre and post audit records together.





Now if the tables hold all the records, my first thought was to simply do a mass insert. But as the event ties the pairs of pre and post records together I can€™t just do a mass inset into the database. Thus when I first thought about doing this row by row triggers is great for me as it keeps the logic fairly simple. But if i only get one bit of the apple (as it were) does anyone have any suggestions as to about I could go about. Is my only option to run through a cursor on the inner join of the pre and the post data and on each loop create my event and then insert the pre and post shots??



Any help is appreciated.

Anthony