Add a datestamp to an existing table

#11

--CELKO-- wrote:

Quote:

you are jumping to conclusions without knowing anything about the problem. What if there are no legal issues at all?

1) I live in the Untied States where we have one lawyer for every 400
citizens. Look up the figures for Japan to get an idea how bad this
is. So, there is a always a legal issue

And if there isn't one
now, there will next week.

2) Putting audit data in the same table is like putting the LOG file
on the same hard drive as the DB. You guarantee that recovery will be
impossible. An audit trail should require at least two "signatures"
to change an audit entry; this schema allows one user to do that if he
has access to the timestamps. This is simply good programming, even
without the lawyers.

Joe is absolutely correct. In the US, at least, there are always
legal issues. Anyone that pretends they are not around the corner
needs a heavy dose of reality.

An audit trail, by definition, must be unalterable by anyone with
access to the hardware or software. And in many countries that isn't
just who altered the data ... but who saw it as it is, for example,
under HIPAA.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

#12

On Dec 5, 12:35 pm, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

Quote:

On Wed, 5 Dec 2007 06:02:59 -0800 (PST), scoots987 wrote:
On Dec 4, 4:33 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hugo Kornelis (h... (AT) perFact (DOT) REMOVETHIS.info.INVALID) writes:
Another column could be used to track who changed it. Is there a neat
way to determine username, machine name, MAC address or any other
method? I pretty wide open on this one as I am not sure what I want to
use here.

Check out SUSER_SNAME() and HOST_NAME() in Books Online.

There's far too many functions for the current user for it to be
healthy.

The best to use, though, is probably original_login(), because the
others (SESSION_USER, SYSTEM_USER, suser_sname() etc) gives incorrect
information if there is an EXECUTE AS clause somewhere along the line.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the replies.

Are there examples of creating a trigger for tracking table data
changes? Since I never created one before.

Thanks again...

Hi scooots987,

If you choose a design with a seperate history table that holds all
versions of the data (keyed on the primary key of the table plus a
datetime column that records the moment any change took place), use
something like this

CREATE TRIGGER ins_MyTable
ON MyTable
FOR INSERT
AS
INSERT INTO HistoryOfMyTable
(KeyCol1, KeyCol2, DataCol1, DataCol2,
TypeOfChange, MomentOfChange, Whodunnit)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2,
'Insert', CURRENT_TIMESTAMP(), ORIGINAL_LOGIN()
FROM inserted;

CREATE TRIGGER upd_MyTable
ON MyTable
FOR UPDATE
AS
INSERT INTO HistoryOfMyTable
(KeyCol1, KeyCol2, DataCol1, DataCol2,
TypeOfChange, MomentOfChange, Whodunnit)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2,
'Update', CURRENT_TIMESTAMP(), ORIGINAL_LOGIN()
FROM inserted;

CREATE TRIGGER del_MyTable
ON MyTable
FOR DELETE
AS
INSERT INTO HistoryOfMyTable
(KeyCol1, KeyCol2, DataCol1, DataCol2,
TypeOfChange, MomentOfChange, Whodunnit)
SELECT KeyCol1, KeyCol2, NULL, NULL,
'Delete', CURRENT_TIMESTAMP(), ORIGINAL_LOGIN()
FROM deleted;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

Thank you Hugo. Among the other replies, I appreciate the example. I
will review this and make the necessary changes.

#13

On Dec 6, 11:12 am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

You need to look a third party audit tool that will meet the legal
requirements. Think about what happens when you do a delete or the DB
crashes. What are you going to tell the court?

Thanks Joe, I own one of your books. I need to dig into it again.

May I ask why legal requirements came about in this discussion? I
don't see any in a custom app. What third party tool would you pitch?

Thanks

#14

On Dec 8, 1:50 pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

you are jumping to conclusions without knowing anything about the problem. What if there are no legal issues at all?

1) I live in the Untied States where we have one lawyer for every 400
citizens. Look up the figures for Japan to get an idea how bad this
is. So, there is a always a legal issue

And if there isn't one
now, there will next week.

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs [legal environment, SOX] of his tribe and island [Austin, TX]
are the laws of nature [entire IT community]" - Caesar and Cleopatra;
George Bernard Shaw 1898

Quote:

2) Putting audit data in the same table is like putting the LOG file
on the same hard drive as the DB. You guarantee that recovery will be
impossible.

Using audit trail for recovery is a very strange idea, there is
nothing in OP to suggest such a requirement.

#15

Quote:

May I ask why legal requirements came about in this discussion?

Because we live in a country with too many lawyers

If you don't
need to reconstruct the sequence of events, why timestamp the rows at
all?

Quote:

I don't see any in a custom app.

In a small, *private* application probably not much need for an audit
trail. But what you need is a question to ask the lawyer and the
accountant, if you want to be safe ...

Quote:

What third party tool would you pitch?

I have no favorites; you might want to post a request for opinions and
experiences from people here who are working in the same industry,
same size DB, etc.

#16

On Dec 10, 5:12 pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

May I ask why legal requirements came about in this discussion?

Because we live in a country with too many lawyers

If you don't
need to reconstruct the sequence of events, why timestamp the rows at
all?

I don't see any in a custom app.

In a small, *private* application probably not much need for an audit
trail. But what you need is a question to ask the lawyer and the
accountant, if you want to be safe ...

What third party tool would you pitch?

I have no favorites; you might want to post a request for opinions and
experiences from people here who are working in the same industry,
same size DB, etc.

Thanks Joe, I wouldn't use timestamps to reconstruct the data. But may
be I am. I am going to have 3 copies of a database. Two will be off-
site. The one on-site will be the source. When records are updated I
would like to send the changes to the other locations. I haven't
decided the whole process here just determining what needs to be done.
Time is my biggest challenge. I am only doing this for the short run
as we are discussing a rewrite down the road. I can not guarantee a
solid internet connection just one I can manage. I don't see a lot of
changes, may be 50 a week.

Without getting into the history of this too much. I inherited a
database that other departments need to see and link to. Basically
this is an event database. Showing what events the customer has
attended. Depending if the customer has met a certain set of
achievements, like numerous involvements.

Getting into it too much... sorry...

#17

Have you looked into replication?

J

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Add a datestamp to an existing table

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Add a datestamp to an existing table - 12-08-2007 , 05:37 PM

Re: Add a datestamp to an existing table - 12-09-2007 , 09:41 AM

Re: Add a datestamp to an existing table - 12-09-2007 , 09:46 AM

Re: Add a datestamp to an existing table - 12-10-2007 , 11:03 AM

Re: Add a datestamp to an existing table - 12-10-2007 , 05:12 PM

Re: Add a datestamp to an existing table - 12-11-2007 , 06:17 AM

Re: Add a datestamp to an existing table - 12-11-2007 , 06:46 AM