SQL SERVER 2000 trigger for Audit trail

• R.B.Verma

  SSC Enthusiast

  Points: 158

  More actions

  July 28, 2004 at 11:26 pm

  #83454

   

   

  All tables will contain create and modify Time Stamps as well as create and modify users as following fields:

  ColumnName	Type	Comments
  Create_ts	Datetime	Create Timestamp
  Modify_ts	Datetime	Modify Timestamp
  Create_user	Varchar(20)	Name of the user that created the entry
  Modify_user	Varchar(20)	Name of the user that last modified the entry

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  July 29, 2004 at 1:33 am

  #516706

  Something like this should get you most of the way:

  create TRIGGER [name] ON

  FOR INSERT

  AS

  update

  set created_by = SUSER_SNAME(),

  last_modified_by = SUSER_SNAME(),

  date_created = GETDATE(),

  date_last_modified = GETDATE()

  from inserted

  where .Seqno = inserted.seqno;

  create TRIGGER [name] ON

  FOR UPDATE

  AS

  update

  set last_modified_by = SUSER_SNAME(),

  date_last_modified = GETDATE()

  from inserted

  where .Seqno = inserted.Seqno;

  ---