kresa kresa - 17 days ago 5
SQL Question

Create trigger upon each table creation in SQL Server 2008 R2

I need to create an

Audit
table that is going to track the actions (insert, update, delete) of my tables in the database and add new row with date, row id, table name and a few more details, so I will know what action happened and when.

So basically from my understanding I need a trigger for each table which is going to track insert/update/delete and a trigger on the database which is going to track new table creation.

My main problem is understanding how to connect between those things so when a new table is being created a trigger will be created for that table which is going to track the actions and add new rows for the Audit table as needed.

Is it possible to make a DDL trigger for
create_table
and inside of it another trigger for insert / update / delete ?

Answer

What you're hoping for is not possible. And I'd strongly advise that you'd be better off thinking about what you really want to achieve at a business level with auditing. It will yield a much simpler and more practical solution.


First up

...trigger on the database which is going to track new table creation.

I cannot stress enough how terrible this idea is. Who exactly has such unfettered access to you database that they can create tables without going through code-review and QA? Which should of course be on the gated pathway towards production. Once you realise that schema changes should not happen ad-hoc, it's patently obvious that you don't need triggers (which are by their very nature reactive) to do something because the schema changed.

Even if you could write such triggers: it's at a meta-programming level that simply isn't worth the effort of trying to foresee all possible permutations.

Better options include:

  • Requirements assessment and acceptance: This is new information in the system. What are the audit requirements?
  • Design review: New table; does it need auditing?
  • Test design: How to test an audit requirements?
  • Code Review: You've added a new table. Does it need auditing?
  • Not to mention features provided by tools such as:
  • Source Control.
  • Db deployment utilities (whether home-grown or third party).

Part two

... a trigger will be created for that table which is going to track the actions and add new rows for the Audit table as needed.

I've already pointed out why doing the above automatically is a terrible. Now I'm going a step further to point out that doing the above at all is also a bad idea.

It's a popular approach, and I'm sure to get some flack from people who've nicely compartmentalised their particular flavour of it; swearing blind how much time it "saves" them. (There may even be claims to it being a "business requirement"; which I can assure you is more likely a misstated version of the real requirement.)

There are fundamental problems with this approach:

  • It's reactive instead of proactive. So it usually lacks context.
  • You'll struggle to audit attempted changes that get rolled back. (Which can be a nightmare for debugging and usually violates real business audit requirements.)
  • Interpreting audit will be a nightmare because it's just raw data. The information is lost in the detail.
  • As columns are added/renamed/deleted your audit data loses cohesion. (This is usually the least of problems though.)
  • These extra tables that always get updated as part of other updates can wreak havoc on performance.
  • Usually this style of auditing involves: every time a column is added to the "base" table, it's also added to the "audit" table. (This ultimately makes the "audit" table very much like a poorly architected persistent transaction log.)
  • Most people following this approach overlook the significance of NULLable columns in the "base" tables.

I can tell you from first hand experience, interpreting such audit trails in any but the simplest of cases is not easy. The amount of time wasted is ridiculous: investigating issues, training others to be able to interpret them correctly, writing utilities to try make working with these audit trails less painful, painstakingly documenting findings (because the information is not immediately apparent in the raw data).

If you have any sense of self-preservation you'll heed my advice.


Make it great

(Sorry, couldn't resist.)

A better approach is to proactively plan for what needs auditing. Push for specific business requirements. Note that different cases may need different auditing techniques:

  • If user performs action X, record A details about the action for legal traceability.
  • If user attempts to do Y but it prevented by system rules, record B details to track rule system integrity.
  • If user fails to log in, record C details for security purposes.
  • If system is upgraded, record D details for troubleshooting.
  • If certain system events occur, record E details ...

The important thing is that once you know the real business requirements, you won't be saying: "Uh, let's just track everything. It might be useful." Instead you'll:

  • Be able to produce a cleaner more appropriate and reliable design for each distinct kind of auditing.
  • Be able to test that it behaves as required!
  • Be able to use the audit data more easily whenever it's needed.