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

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

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Dec 4, 4:33 pm, Erland Sommarskog <esq...@sommarskog.se> 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/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...

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

>On Dec 4, 4:33 pm, Erland Sommarskog <esq...@sommarskog.se> 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/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

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?

--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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Dec 6, 11:12 am, --CELKO-- <jcelko...@earthlink.net> 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?

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