Audit trigger not logging deletions

I Have created a trigger to log all activity on a table. It is logging all details perfectly for updates and additions, however it is not writing a row when you merely delete a row. My code is below - can anyone spot where I'm goiing wrong?





ALTER  trigger *Tr_myTableName *on dbo.myTableName for insert, update,
delete as
 
      declare @bit int ,     
      @field int ,     
      @maxfield int ,     
      @char int ,     
      @fieldname varchar(*700*) ,     
      @TableName varchar(*700*) ,     
      @PKCols varchar(*700*) ,     
      @sql varchar(*8000*),     
      @UpdateDate varchar(*100*) ,     
      @UserName varchar(*800*) ,     
      @Type char(*9*) ,     
      @PKSelect varchar(*6500*),     
           
 
      select @TableName = 'myTableName'     
      select       @UserName = system_user ,           
 
 
      if exists (select * from inserted)           
            if exists (select * from deleted)                 
                  select @Type = '*UDT' *          
            else                 
                  select @Type = 'INS'     
      else           
            select @Type = 'DEL'               
 
      select *  into #del from deleted 
      select *  into #ins from inserted           
          
      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           
     
      select @PKSelect = coalesce(@PKSelect+'+','') + '''<' +
COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME
+',d.' + 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      
    
     
         
 
      select @field = 0, @maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName     
     
      while @field < @maxfield     
            begin           
                  select @field = min(ORDINAL_POSITION)
                  from INFORMATION_SCHEMA.COLUMNS
                  where TABLE_NAME = @TableName and ORDINAL_POSITION >
@field           
 
                  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 or
@Type in ('INS','DEL')           
                        begin                 
                              select @fieldname = COLUMN_NAME
                              from INFORMATION_SCHEMA.COLUMNS
                              where TABLE_NAME = @TableName and
ORDINAL_POSITION = @field                 
                              select @sql =             'insert
*tblAudit (audType, TblNam, primaryKey, FldName,
                                                  Old, New, audDat,
User*)'                 
                              *select @sql = @sql +       ' 
			      select @sql = @sql +       ',''' +
@Type + '''' *                
                              select @sql = @sql +       ',''' +
@TableName + ''''                 
                              select @sql = @sql +       ',' + @PKSelect
                
                              select @sql = @sql +       ',''' +
@fieldname + ''''                 
                              select @sql = @sql +      
',convert(varchar(1000),d.' + @fieldname + ')'                 
                              select @sql = @sql +      
',convert(varchar(1000),i.' + @fieldname + ')'                 
                              select @sql = @sql +       ',''' +
@UpdateDate + ''''                 
                              select @sql = @sql +       ',''' +
@UserName + ''''                 
                              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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Open in new window

fuerteventuraAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Try changing from:

 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')      

to

 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('ADD','DEL')      
0
 
fuerteventuraAuthor Commented:
I found myself what was wrong late yesterday - it was what you stated so many thanks for taking the time to look and have 500 points! Cheers.
0
All Courses

From novice to tech pro — start learning today.