Re: Viewing SQl transacion logs...

From: Brad Pears (donotreply_at_notreal.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 16:22:18 -0500

Thanks for that. Basically that is exactly what we are doing in our Access
DB right now. However, as you can imagine, the overhead to maintain an audit
trail of changes gets larger as time carry's on. I was thinking that since
we are converting to SQL server, if they were already keepijng those audit
trails, maybe I wouldn;t have to create triggeres to do this type of thing
anymore...

Thanks for your help...
"Ron Hinds" <__NoSpam__ron@__ramac__.com> wrote in message
news:ODHFcQTvEHA.3896@TK2MSFTNGP09.phx.gbl...
> "Brad Pears" <donotreply@notreal.com> wrote in message
> news:%23g2cKtSvEHA.2804@TK2MSFTNGP14.phx.gbl...
> > Since SQL server keeps track of every modification to a table during a
> > transaction, if I change a field value in a single row of a table, where
> do
> > I look to find the log file that was generated that will tell me what
the
> > old value was and what it is now?
> >
> > Also, is there a way to have SQL keep track of specifc changes
regardless
> of
> > whether or not the change was part of a transaction?
> >
> > Thanks,
> >
> > Brad
>
> The SQL Server Transaction logs keep track of *all* changes to the
database,
> whether or not they were contained in formal transactions (BEGIN TRANS,
> etc.). That is, *if* you have SQL Server's Recovery model set to either
Full
> or Bulk-logged. The transaction logs created are not viewable AFAIK; they
> are for restoring the database after a crash, etc. The scenario is: your
db
> server crashes - you rebuild it, then restore the last full backup, then
> apply the transaction log(s) and Bingo! you're back to where you were
before
> the problem!
>
> Now, as to whether SQL server can track specific changes. The short answer
> is yes. But it will require you to study some advanced concepts in SQL
Books
> Online, like Triggers. Basically a Trigger is code that executes in
> repsponse to a SQL statement. So you can have Insert, Update and Delete
> triggers on any table to create an audit trail. In the code of a trigger,
> you can write the data that is being changed to an audit table along with
> the user's name, date and time, etc.
>
>

Relevant Pages

  • Re: Audit logging an adp
    ... Allen Browne's audit code. ... Some people may be tempted to suggest using SQL Server triggers, ... but without an adequate security model your audit trail ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SP with RETURN @@IDENTITY returning 0??
    ... No triggers, I did try the SCOPE_IDENTITY and I have the same result. ... > Columnist, SQL Server Professional ... >> Have you tried putting the RETURN outside of the transaction? ... >> Toronto, ON Canada ...
    (microsoft.public.sqlserver.programming)
  • Re: Audit logging an adp
    ... if Access MDB had triggers; I would probably be using it right now lol. ... Allen Browne's audit code. ... Some people may be tempted to suggest using SQL Server triggers, ... but without an adequate security model your audit trail ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Audit logging an adp
    ... connection, then you can pass in any username you like and it will remain local to the database connection. ... The triggers can read this data and store whatever value you pass in as the user ID. ... I sell a SQL Server audit product which automatically creates audit triggers for you, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Viewing SQl transacion logs...
    ... is there a way to have SQL keep track of specifc changes regardless ... > whether or not the change was part of a transaction? ... The SQL Server Transaction logs keep track of *all* changes to the database, ... triggers on any table to create an audit trail. ...
    (microsoft.public.sqlserver.odbc)
Loading