First, I'd like to thank G. Vijayakumar for a very thoughtful analysis and comparison on the operational differences between stored procedures and triggers.

Next, I'd like to remind all DBAs that in today's world (at least in the US), you not only have to consider the maintenance issues surrounding your database design, you have to consider the business issues, specifically compliance.

Having done my share of stored procedure & trigger coding, I can readily empathize with G. Vijayakumar on the difference between maintaining 450 versus 900 db code objects.

However, when I consider the compliance requirements of an audit trail, I have to ask which is the better approach: a stored procedure that modifies both the data table and the audit table, which can be altered at any time by someone who has ALTER PROCEDURE permissions, or a stored procedure which modifies the data table and then triggers a corresponding modification to the audit table, with trigger code that can be secured separately from the stored procedure code.

Let's say that G Vijayakumar's #3 plan was in place -- modify both user data and audit table via the same stored proc. What would happen if someone who wasn't fully aware of the audit requirements were to create a stored procedure that modified the user data, but failed to modify the corresponding audit table? G Vijayakumar's solution #3 depends on use rules -- whoever writes a stored proc that's going to modify user data has to remember to also modify the corresponding audit table(s). That's dangerous, because use rules are always violated -- not intentionally, usually accidentally, but when they're violated, they're violated and you're missing data out of the audit tables. Compliance auditors are not going to be happy about this, and the senior DBA is going to take a hit to his or her reputation, perhaps to the point of not being trusted with corporate data... not a good scenario.

That's my perspective... so I'm leaning towards solution #2, separating stored proc from trigger, so that 1) I can manage the CREATE and ALTER permissions on both, and 2) I can be assured that on ANY modification to the user data there'll be a record written to the audit tables.

Thanks again for a very useful article!