Although this thread is ancient history, a couple more points -
Microsoft's implementation of triggers disallows the modification of data being persisted. So if you wanted a field named date_last_modified on your data table and you wanted to use a trigger to set that value to getDate() via an update trigger, you can't. I made a note some time ago that the MS implementation of triggers forces developers to write things like cursors or to issue additional update statements from within triggers - which makes no sense to me. If you issue an update from within a trigger (against the table being triggered - for example, to set date_last_modified), won't that update re-fire the trigger? Apparently it doesn't - but that's MS for you. With other RDBMSes you can simply catch an update and modify the value at update time if you want to.
I have another note - "It turns out, from discussion on dba.stackexchange, that MS implements triggers as batches of updates at a time - so you can access inserted and deleted as complete rowsets, but not modify values. So for example, to determine whether a value in a column has changed, you have to join inserted and deleted on the primary key. Apparently, if your update is going to update 150,000 rows (as it does with the clean script), then the claused to check whether 'EXISTS' rows in inserted and deleted that join on ID but have different values in the column of interest... results in full table scans of those sets of 150,000 rows. Insane." Where I mention "clean script" - this is invoked when we restore a production backup to another environment such as for testing. This script will modify data values for things like server/connection settings, email addresses and so on. If you have 150,000 rows of client data and each client has an email address and you want your test environment to set that email address to something like email@example.com and if you have an audit on that table using triggers, then the above scenario applies and your clean script will die a slow death. Very slow. Or put another way - you now have to modify your clean script to disable the trigger before updating. Whether or not the scenario is valid, it's a point to bear in mind - that updates to large amounts of data on tables with audit triggers can slow things down hugely. For a clean script, maybe you can disable the trigger, but if your production environment has a valid need to update large quantities of data, well, then your trigger may impact performance. I never had this problem with other RDBMSes but per my note, MS seems to handle triggers differently.