Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Audit Trigger

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
kkelley6
Asked:
kkelley6
  • 2
  • 2
1 Solution
 
kkelley6Author Commented:
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
 
nigelrivettCommented:
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
 
kkelley6Author Commented:
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
 
nigelrivettCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now