?
Solved

Audit Trigger

Posted on 2003-03-01
4
Medium Priority
?
388 Views
Last Modified: 2012-06-27
I am looking to audit inserts,updattes and deletes on my various tables.

What I am looking to do is when a record in the db is :

- inserted - capture the date, user that inserted
- updated - capture the date,user that updated
- deleted - capture the date,user that deleted the record but I am just going to label it as deleted and prevent the delete.

here is an example of one of the tables i am looking to audit.

CREATE TABLE [dbo].[SENTRY] (
     [ID] [varchar] (20) NOT NULL ,
     [ProfileManager] [varchar] (50) NOT NULL ,
     [SubSource] [varchar] (50) NOT NULL ,
     [SentrySeverity] [varchar] (8) NOT NULL ,
     [MonStatus] [varchar] (8) NOT NULL ,
     [Schedule] [varchar] (250) NOT NULL ,
     [Monitor] [varchar] (250) NOT NULL ,
     [Keyphrase] [varchar] (100) NOT NULL ,
     [Probearg] [varchar] (250) NOT NULL ,
     [Condition] [varchar] (100) NOT NULL ,
     [Interval] [varchar] (50) NOT NULL ,
     [TecSeverity] [varchar] (8) NOT NULL ,
     [Email] [varchar] (1024) NOT NULL ,
     [Logfile] [varchar] (250) NOT NULL ,
     [Icon] [varchar] (100) NOT NULL ,
     [Notice] [varchar] (100) NOT NULL ,
     [Popup] [varchar] (100) NOT NULL ,
     [RunProgram] [varchar] (250) NOT NULL ,
     [OncallLabel] [varchar] (100) NULL ,
     [DocLabel] [varchar] (512) NULL ,
     [Product] [varchar] (50) NULL ,
     [SubProduct] [varchar] (50) NULL ,
     [Comments] [varchar] (1024) NULL ,
     [TsengNotes] [varchar] (1024) NULL ,
     [DescriptionLabel] [varchar] (250) NULL ,
     [SecOncallLabel] [varchar] (100) NULL ,
     [TerOncallLabel] [varchar] (100) NULL ,
     [CustomScript] [varchar] (250) NULL ,
     [CustomRelationship] [varchar] (50) NULL,
        [RowState] [varchar] 20 NULL,
        [RowUpdated] [varchar] 50 NULL,
        [RowUpdatedBy] [varchar] 20 NULL
) ON [PRIMARY]
GO


I have a column in each table called RowState,RowUpdated,RowUpdatedBy.

I am looking to record the state of that row (INSERTED,DELETED,UPDATED) in the table's RowState.  The GETDATE in the RowUpdated and the RowUpdatedBy with the username.


My trouble has been figuring out what is being inserted,updated and deleting and ensuring that those rows are updated correctly. I want the abillity to determine if ANY of the records where updated in the row.
0
Comment
Question by:kkelley6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:kkelley6
ID: 8046878
Actually,  if it is easier/better to consolidate the audit into it's own table ...I am open to that.  due to the number of tables I have I would just need to capture the before and after values.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8046915
See
www.nigelrivett.com
Audit trail trigger

This will table a table and create an audit trail trigger for it.

It will audit to
create table Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000))
go

And put in the table, field names and before and after values of any fields changed.
For your requirement you just need to add getdate() and system_user to the table.

0
 

Author Comment

by:kkelley6
ID: 8050989
nigelrivett, this seems to be the way to go.  I added the trigger to the table and with every modification I get the 'no pk' error message.  but tthe table does have one.

CREATE TABLE [dbo].[SENTRY] (
     [ID] [varchar] (20) NOT NULL ,
     [ProfileManager] [varchar] (50) NOT NULL ,
     [SubSource] [varchar] (50) NOT NULL ,
     [SentrySeverity] [varchar] (8) NOT NULL ,
     [MonStatus] [varchar] (8) NOT NULL ,
     [Schedule] [varchar] (250) NOT NULL ,
     [Monitor] [varchar] (250) NOT NULL ,
     [Keyphrase] [varchar] (100) NOT NULL ,
     [Probearg] [varchar] (250) NOT NULL ,
     [Condition] [varchar] (100) NOT NULL ,
     [Interval] [varchar] (50) NOT NULL ,
     [TecSeverity] [varchar] (8) NOT NULL ,
     [Email] [varchar] (1024) NOT NULL ,
     [Logfile] [varchar] (250) NOT NULL ,
     [Icon] [varchar] (100) NOT NULL ,
     [Notice] [varchar] (100) NOT NULL ,
     [Popup] [varchar] (100) NOT NULL ,
     [RunProgram] [varchar] (250) NOT NULL ,
     [OncallLabel] [varchar] (100) NULL ,
     [DocLabel] [varchar] (512) NULL ,
     [Product] [varchar] (50) NULL ,
     [SubProduct] [varchar] (50) NULL ,
     [Comments] [varchar] (1024) NULL ,
     [TsengNotes] [varchar] (1024) NULL ,
     [DescriptionLabel] [varchar] (250) NULL ,
     [SecOncallLabel] [varchar] (100) NULL ,
     [TerOncallLabel] [varchar] (100) NULL ,
     [CustomScript] [varchar] (250) NULL ,
     [CustomRelationship] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SENTRY] WITH NOCHECK ADD
     CONSTRAINT [PK_SENTRY1] PRIMARY KEY  NONCLUSTERED
     (
          [ID],
          [ProfileManager],
          [SubSource],
          [SentrySeverity]
     ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[SENTRY] ADD
     CONSTRAINT [FK_SENTRY_DESCRIPTION] FOREIGN KEY
     (
          [DescriptionLabel]
     ) REFERENCES [dbo].[DESCRIPTION] (
          [DescriptionLabel]
     ) NOT FOR REPLICATION ,
     CONSTRAINT [FK_SENTRY_DOC] FOREIGN KEY
     (
          [DocLabel]
     ) REFERENCES [dbo].[DOC] (
          [DocLabel]
     ) NOT FOR REPLICATION ,
     CONSTRAINT [FK_SENTRY_ONCALL] FOREIGN KEY
     (
          [OncallLabel]
     ) REFERENCES [dbo].[ONCALL] (
          [OncallLabel]
     ) NOT FOR REPLICATION
GO

0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 800 total points
ID: 8051187
Try

CREATE TABLE [dbo].[SENTRY] (
    [ID] [varchar] (20) NOT NULL ,
    [ProfileManager] [varchar] (50) NOT NULL ,
    [SubSource] [varchar] (50) NOT NULL ,
    [SentrySeverity] [varchar] (8) NOT NULL ,
    [MonStatus] [varchar] (8) NOT NULL ,
    [Schedule] [varchar] (250) NOT NULL ,
    [Monitor] [varchar] (250) NOT NULL ,
    [Keyphrase] [varchar] (100) NOT NULL ,
    [Probearg] [varchar] (250) NOT NULL ,
    [Condition] [varchar] (100) NOT NULL ,
    [Interval] [varchar] (50) NOT NULL ,
    [TecSeverity] [varchar] (8) NOT NULL ,
    [Email] [varchar] (1024) NOT NULL ,
    [Logfile] [varchar] (250) NOT NULL ,
    [Icon] [varchar] (100) NOT NULL ,
    [Notice] [varchar] (100) NOT NULL ,
    [Popup] [varchar] (100) NOT NULL ,
    [RunProgram] [varchar] (250) NOT NULL ,
    [OncallLabel] [varchar] (100) NULL ,
    [DocLabel] [varchar] (512) NULL ,
    [Product] [varchar] (50) NULL ,
    [SubProduct] [varchar] (50) NULL ,
    [Comments] [varchar] (1024) NULL ,
    [TsengNotes] [varchar] (1024) NULL ,
    [DescriptionLabel] [varchar] (250) NULL ,
    [SecOncallLabel] [varchar] (100) NULL ,
    [TerOncallLabel] [varchar] (100) NULL ,
    [CustomScript] [varchar] (250) NULL ,
    [CustomRelationship] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SENTRY] WITH NOCHECK ADD
    CONSTRAINT [PK_SENTRY1] PRIMARY KEY  NONCLUSTERED
    (
         [ID],
         [ProfileManager],
         [SubSource],
         [SentrySeverity]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

create table Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000))
go


create trigger tr_SENTRY on SENTRY for insert, update, delete
as

declare @bit int ,
      @field int ,
      @char int ,
      @fieldname varchar(128) ,
      @TableName varchar(128) ,
      @PKCols varchar(1000) ,
      @sql varchar(2000)
      
      select @TableName = 'SENTRY'
      
      select * into #ins from inserted
      select * into #del from deleted
      
      -- Get primary key columns for full outer join
      select      @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
      from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where       pk.TABLE_NAME = @TableName
      and      CONSTRAINT_TYPE = 'PRIMARY KEY'
      and      c.TABLE_NAME = pk.TABLE_NAME
      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
      
      if @PKCols is null
      begin
            raiserror('no PK on table %s', 16, -1, @TableName)
            return
      end
      
      select @field = 0
      while @field < (select max(colid) from syscolumns where id = (object_id(@TableName)))
      begin
            select @field = @field + 1
            select @bit = (@field - 1 )% 8 + 1
            select @bit = power(2,@bit - 1)
            select @char = ((@field - 1) / 8) + 1
            if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
            begin
                  select @fieldname = name from syscolumns where colid = @field and id = object_id(@TableName)
                  select @sql =             'insert Audit (TableName, FieldName, OldValue, NewValue)'
                  select @sql = @sql +       ' select ''' + @TableName + ''''
                  select @sql = @sql +       ',''' + @fieldname + ''''
                  select @sql = @sql +       ',convert(varchar(1000),d.' + @fieldname + ')'
                  select @sql = @sql +       ',convert(varchar(1000),i.' + @fieldname + ')'
                  select @sql = @sql +       ' from #ins i full outer join #del d'
                  select @sql = @sql +       @PKCols
                  select @sql = @sql +       ' where i.' + @fieldname + ' <> d.' + @fieldname
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)'
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)'
                  
                  exec (@sql)
            end
      end

go


insert SENTRY  select 1, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a'

update SENTRY set Logfile = 'd'

select * from Audit
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question