A simple problem with SQL SERVER 2000

[Rex]

Hi,

I have about 5 users and I want to keep a track of which user changed
what data in a particular table. I am using SQL server 2000. One
solution that I have thought about is to have two extra columns in the
tables where I want to keep track of who modified or added new
records.

So accordingly I will have two extra columns as 'Modifier' and
'Time_Stamp' in a table. And as soon as any user modifies or adds a
new record, this columns should be filled with its username and a
timestamp when he added or modified a particular record..

The problem is I dont know how to do this.. any help would be
appreciated

Thanks

 

[Guest]

Hi Rex,

Perhaps you can use this sample as a guide...

Creating an Audit Log
http://allenbrowne.com/AppAudit.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Arvin Meyer [MVP]]

If you are using Access as a front-end, then use a form and write the values
to the table in the form's afterupdate event. You can get the username from:

http://www.mvps.org/access/api/api0008.htm

If you are writing to the tables without a form, you need to use an Update
Trigger. The last time I did that was some time ago using SQL 6.5 and I did
something like:

UPDATE MyTable SET ChangeName = (select nt_username
FROM master..sysprocesses
Where spid=@@SPID )
WHERE ID IN
(SELECT ID FROM Inserted)

I may have the syntax slightly off, so you may want to ask in a SQL-Server
newsgroup.

 

[Bill Edwards]

Set the default values of Modifer and Time_Stamp in the table design:

ALTER TABLE Customer ADD CONSTRAINT Df_Customer_Modifier DEFAULT
Suser_Sname() FOR Modifier
ALTER TABLE Customer ADD CONSTRAINT Df_Customer_Time_Stamp DEFAULT GetDate()
FOR Time_Stamp

This will take care of the cases when a new record is added.

Then create an UPDATE TRIGGER to take care of the times when a record is
edited.

CREATE TRIGGER Trig_Update_Customer
ON Customer
FOR UPDATE
AS
DECLARE @UserName varchar(50)
DECLARE @DateTime datetime
SET @UserName = Suser_Sname()
SET @DateTime = GetDate()

UPDATE Customer
SET Modifier = @UserName, Time_Stamp = @DateTime
FROM Customer, Inserted
WHERE Customer.CustomerId = Inserted.CustomerId
' This assumes that CustomerId is the primary key of the customer table.

 

[Rex]

Thanks Bill that worked really well.. but is there any way where I can
have a general trigger which can be used for any tables for doing the
same thing..

Thanks

 

[aaron.kempf]

yeah.. well the TSQL won't be the same-- but you can write something
out using VB in order to generate this.

this is one of the main reasons I've wanted Microsoft to come out with
syntax like this:

Select * {LIKE '%ID') from myTable
Select * {LIKE '%ID' ABC) from myTable
Select * {NOT LIKE '%entered') from myTable

I have worked with 20 databases the past 10 years that have more than
100 columns.
it is ridiculous that Microsoft is so anti-wildcard.

This isn't a place for theory; it is a place where we need more
functionality.

SELECT STAR is the most powerful command in our toolkit; it is
ridiculous that it is 'frowned upon' and we can't have this
funcitonality

 

[Bill Edwards]

AFAIK, there is no facility to create a database level DML trigger.

One possibility, would be to retrieve the names of all of your user defined
tables (SELECT * FROM sysobjects WHERE xType = 'U') and then to use this to
build a string variable that contains the ALTER TABLE and CREATE TRIGGER
statements and use the EXECUTE statement to execute the dynamic SQL in the
string.

-- Execute example:
DECLARE @TableName varchar(50)
SET @TableName = 'Customers'
EXECUTE ('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT
Df_Customer_Modifier')