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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.