Global Triggers?

Hi all,
I have been asked to help design a general audit trail for several
applications that use SQL server.
I would like to create a 'Global Trigger' (for lack of a better term) that
can grab any insert, update or delete on a table across databases on the same
server, and log that data into a seperate database, which would be excluded
from the auditing, since it would create an infinite loop rather quickly.
Is there such a thing? How difficult would this be? I would ASSUME that
this had been tried before. If it is impossible to go this route, is there
something close to this I can do?
We are in the design phase, and this would be a preferred way to go.
Thanks in advance,
Brad Simon
I have been kicking around ideas for a while for something like this as well
and I am curious to follow the other responses. I don't there is such a
thing as a global trigger.
You could put triggers on each table and log the changes into a 'history'
table but you would need a history table for every table you are logging -
maybe an audit db would make more sense. It would not be difficult to write
a code generator to create these history tables and triggers for you
automatically. One limitation of this is that you are only logging Inserts,
Updates, and Deletes. There is no way to log Select. In the audit project
I have been thinking about they want to know who is looking at the data as
well as changing it.
Another thing we are thinking about is a middle tier that would take an XML
formatted parameter and reformat it into a SQL statement and return the
results. The app would pass in all of it's requests as XML and we would
just log the XML. All the business rules and logic to reformat the XML to
SQL would be in this middle tier. The only caveat to this is that it's not
automatic. If someone decides to bypass this component (and security allows
it) then nothing gets logged.
Like I said I am still in the 'kicking around' stage and wondering what else
people have done. There are probably third party apps that hook directly
into SQL and log everything - kind of like Profiler or Trace, but with an
emphasis geared toward audit as opposed to performance and monitoring.
"Brad Simon" <bsimon@.simondeveloping.com> wrote in message
news:E4C9D7B7-AA8E-4663-9C3D-FBC33554D450@.microsoft.com...
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the
> same
> server, and log that data into a seperate database, which would be
> excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is
> there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
> --
> Thanks in advance,
> Brad Simon
|||Brad Simon wrote:
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the same
> server, and log that data into a seperate database, which would be excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
>
There's no such beast as a global trigger. You'd have to install
triggers on every table you wanted to audit and have them communicate
with an centralized stored procedure in an audit database. But, by the
time you gathered all the necessary information which is available only
in the trigger, the centralized stored procedure wouldn't have much to do.
You can write generic trigger code which tries to dynamically determine
the columns on the table the trigger is installed on (so that you have a
single trigger script which figures out the table it is attached to at
runtime), but this is extremely inefficient and will unnecessarily
degrade performance as the trigger does runtime discovery of columns
every single time a data chaneg occurs.
You really can't avoid writing table-specific triggers for every table
you want to audit, hard-coded to the columns in each table. Your best
bet would be to write some trigger-generating code which sniffs out a
table's columns from metadata and produces a custom CREATE TRIGGER
script for that table.
You can have a look at our OmniAudit product which will do all the work
of creating and installing audit triggers on all tables automatically.
Steve Troxell
http://www.krell-software.com