Add a datestamp to an existing table

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

Re: Add a datestamp to an existing table

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

Re: Add a datestamp to an existing table

Hugo Kornelis (hugo@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Add a datestamp to an existing table

On Dec 4, 4:33 pm, Erland Sommarskog wrote:
> Hugo Kornelis (h...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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...

Re: Add a datestamp to an existing table

scoots987 (scoots987@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Add a datestamp to an existing table

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

>On Dec 4, 4:33 pm, Erland Sommarskog wrote:
>> Hugo Kornelis (h...@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, esq...@sommarskog.se
>>
>> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
>> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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

Re: Add a datestamp to an existing table

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?

Re: Add a datestamp to an existing table

--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/mi_hb4692/is_200212/ai_n1 7809730
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Re: Add a datestamp to an existing table

On Dec 6, 11:12 am, --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?

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