I've never been a big fan of triggers in a database since the existence of triggers is easily overlooked. I've also seen the use of triggers in situations where they where used to fix a bad database design. Specifically trigger were used to enforce constraints on other tables. I've also seen triggers used to move data to another table to keep history of records. I think that use is debatable since a stored procedure can be used to do that and the application should use the stored procedure and not do delete and updates directly. So in this case the trigger was used to protect the system from developers not following the application design. This should not really be needed but...

Anyway, since I've not been working with triggers for quite a while I've never really thought about the options available but today I saw this video (requires free registration) which covers instead of triggers. I think it's a nice feature which simplifies the implementation of protect-from-developers type triggers since you can basically redefine a delete to not do an delete at all and vice versa (if you have the urge to delete data instead of updating it).

But in my opinion triggers is not the best way to handle things in your application. Use a stored procedure since a trigger easily is forgotten but a used stored procedure is not. Also it is easier to follow the flow in a stored procedure over trigger execution. And if you have a nice stored procedure everybody should use but your developer keep screwing things up by using inserts, updates and deletes directly in the database then I would consider adding a trigger preventing direct use. This can be hard since the stored procedure it self will cause the trigger to execute but in my experience the stored procedure typically updates only one record at the time so an easy way to try to find developer "abuse" is to prevent updates of more than one row at the time. That has been enough for me so far.

Preemptive comment response: I know instead of triggers is nothing new and has been available for some time but I've just not been interested in triggers for the last 9 years so please forgive me...