|
|
Posted by Hugo Kornelis on 12/05/07 18:35
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
Navigation:
[Reply to this message]
|