Re: Audit Table

• Previous message: Michelle: "Audit Table"
• In reply to: Michelle: "Audit Table"
• Next in thread: Yuan Shao: "RE: Audit Table"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Mon, 29 Dec 2003 05:32:41 -0500

Front-end code typically has to influence on a trigger. The trigger fires
as a result of the triggering action - INSERT, UPDATE or DELETE. Here's an
example to do what you want:

create trigger triu_MyTable on MyTable after insert, update
as
if @@ROWCOUNT = 0
    return

update MyTable
set
     LastModBy = CURRENT_USER
, LastUpdateDateTime = CURRENT_TIMESTAMP
where
    PK in (select PK from inserted)
go

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Michelle" <michelle.vanden@eglin.af.mil> wrote in message
news:034501c3cdc5$1a2f3a60$a401280a@phx.gbl...
The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert.  I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?

• Previous message: Michelle: "Audit Table"
• In reply to: Michelle: "Audit Table"
• Next in thread: Yuan Shao: "RE: Audit Table"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]