Add a datestamp to an existing table

[scoots987]

Hi all,

I have an existing table(Several actually) that I want to add a column
or two or three. I need to have, I think, at least two columns. One
for Created date and believe this is a no brainer in that I just add
the column and set the default to getdate(). Two, for tracking a
modified date of the row of data. I want to keep it simple. What do I
do to track the modify date of a row in SQL Server 2005? Anytime any
data changes on a row update the update column? Is a trigger the only
way to accomplish this task?

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.

TIA!!!

[Hugo Kornelis]

On Tue, 4 Dec 2007 10:06:24 -0800 (PST), scoots987 wrote:

>Hi all,

I have an existing table(Several actually) that I want to add a column
or two or three. I need to have, I think, at least two columns. One
for Created date and believe this is a no brainer in that I just add
the column and set the default to getdate(). Two, for tracking a
modified date of the row of data. I want to keep it simple. What do I
do to track the modify date of a row in SQL Server 2005? Anytime any
data changes on a row update the update column? Is a trigger the only
way to accomplish this task?

Hi scoots987,

Yes, a trigger is the only way. There is no builtin standard out of the
box functionality for this.

>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.

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

[Erland Sommarskog]

Hugo Kornelis (hu**@perFact.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[scoots987]

On Dec 4, 4:33 pm, Erland Sommarskog <[e-mail]:

Hugo Kornelis ([e-mail]) 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, [e-mail]

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...

[Erland Sommarskog]

scoots987 (sc*******@gmail.com) writes:

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

CREATE TRIGGER mytri ON mytbl FOR INSERT, UPDATE ON
UPDATE mytbl
SET moduser = original_login(),
moddate = getdate()
FROM mytbl m
JOIN inserted i ON m.keycol1 = i.keycol1
AND m.keycol2 = i.keycol2
...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Wed, 5 Dec 2007 06:02:59 -0800 (PST), scoots987 wrote:

>On Dec 4, 4:33 pm, Erland Sommarskog <[e-mail]:

>Hugo Kornelis ([e-mail]) 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, [e-mail]

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

[--CELKO--]

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?

[DA Morgan]

--CELKO-- wrote:

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?

Given what Goldman Sachs, Morgan Stanley, Salomon Smith Barney, Deutsche
Bank and US Bancorp Piper Jaffray were told by the court perhaps:
"How large a check should we write?"

http://www.icpas.org/hc-insight.aspx?id=892
http://findarticles.com/p/articles/m...2/ai_n17809730
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Alex Kuznetsov]

On Dec 6, 11:12 am, --CELKO-- <[e-mail]:

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?

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

[--CELKO--]

> 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.

[DA Morgan]

--CELKO-- wrote:

>> 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
da******@x.washington.edu (replace x with u to respond)

[scoots987]

On Dec 5, 12:35 pm, Hugo Kornelis
<[e-mail]:

On Wed, 5 Dec 2007 06:02:59 -0800 (PST), scoots987 wrote:

On Dec 4, 4:33 pm, Erland Sommarskog <[e-mail]:

Hugo Kornelis ([e-mail]) 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, [e-mail]

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.

[scoots987]

On Dec 6, 11:12 am, --CELKO-- <[e-mail]:

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

[Alex Kuznetsov]

On Dec 8, 1:50 pm, --CELKO-- <[e-mail]:

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

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.

[--CELKO--]

>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.

[scoots987]

On Dec 10, 5:12 pm, --CELKO-- <[e-mail]:

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...

[jhofmeyr]

Have you looked into replication?

J