trigger to record delete active

Question

Hello All,

Someone help me to develop the trigger to record the DML activates. It is working well to record insert and update, but not delete. For example, when I submit script as "delete from _alias_a where id=19", the row of id=19 was deleted in the table, but not be recorded in the log table. Can you help me to modify the trigger to record "delete" action in the trigger also?
Thank you,

ALTER TRIGGER [PMDB].[Trg_Alias_A_audit]
ON [PMDB].[_Alias_A]
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON ;

       DECLARE @bit            INT,  
               @field          INT,  
               @maxfield       INT,  
               @char           INT,  
		       @excludefield   BIT,  
		       @transid		   VARCHAR(128),  
               @fieldname      NVARCHAR(128),  
		       @fieldvalue	   NVARCHAR(1000),  
               @SchemaName     NVARCHAR(128),  
               @TableName      NVARCHAR(128),  
               @PKCols         NVARCHAR(1000),  
               @sql            NVARCHAR(2000),  
               @UpdateDate     VARCHAR(21),  
               @Type           CHAR(6),  
               @PKSelect       NVARCHAR(2000),  

-------------------------------------------------------

	           @Username       NVARCHAR(200)  

	  -- Create grouping ID for entire transaction  
	  SET @transid  = CURRENT_TRANSACTION_ID();	-- In SQL 2016+ this can be replaced with CURRENT_TRANSACTION_ID()  

-------------------------------------------------------

	  set @Username = SUSER_SNAME();  

       --You will need to change @TableName to match the table to be audited.  
       SELECT @SchemaName = OBJECT_SCHEMA_NAME(parent_object_id),  
		 @TableName = OBJECT_NAME(parent_object_id)   
         FROM sys.objects   
         WHERE sys.objects.name = OBJECT_NAME(@@PROCID)  

       SELECT @UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126)  

BEGIN TRY  
	BEGIN TRAN  

       -- Action  
       IF EXISTS (  
              SELECT *  
              FROM   INSERTED  
          )  
           IF EXISTS (  
                  SELECT *  
                  FROM   DELETED  
              )  
               SELECT @Type = 'Update'  
           ELSE  
               SELECT @Type = 'Insert'  
       ELSE  
           SELECT @Type = 'Delete'  

       -- get list of columns  
       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_SCHEMA = @SchemaName  
		      AND pk.TABLE_NAME = @TableName  
              AND CONSTRAINT_TYPE = 'PRIMARY KEY'  
		      AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA  
              AND c.TABLE_NAME = pk.TABLE_NAME  
              AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME  

       -- Get primary key select for insert  
       SELECT @PKSelect = COALESCE(@PKSelect + '+', '')   
              + '''[' + COLUMN_NAME   
              + ']=''+convert(nvarchar(1000),coalesce(i.[' + COLUMN_NAME + '],d.[' + COLUMN_NAME + ']))+'''''  
       FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,  
              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  
       WHERE  pk.TABLE_SCHEMA = @SchemaName  
		      AND pk.TABLE_NAME = @TableName  
              AND CONSTRAINT_TYPE = 'PRIMARY KEY'  
		      AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA  
              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,  
              -- @maxfield = MAX(COLUMN_NAME)   
              @maxfield = -- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName  
              MAX(  
                  COLUMNPROPERTY(  
                      OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),  
                      COLUMN_NAME,  
                      'ColumnID'  
                  )  
              )  
       FROM   INFORMATION_SCHEMA.COLUMNS c  
       WHERE  TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName  


       WHILE @field < @maxfield  
       BEGIN  
           SELECT @field = MIN(  
                      COLUMNPROPERTY(  
                          OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
                          COLUMN_NAME,  
                          'ColumnID'  
                      )  
                  )  
           FROM   INFORMATION_SCHEMA.COLUMNS  
           WHERE  TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName  
                  AND COLUMNPROPERTY(  
                          OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
                          COLUMN_NAME,  
                          'ColumnID'  
                      ) > @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 ('I', 'D')  
           BEGIN  
               SELECT @fieldname = COLUMN_NAME,   
			    @excludefield = 0, --CASE WHEN DATA_TYPE IN ('timestamp','rowversion') THEN 1 ELSE 0 END,  
			    @fieldvalue   = CASE   
					  WHEN DATA_TYPE IN ('timestamp','rowversion') THEN 'convert(nvarchar(max),CONVERT(binary(8),{alias}.' + QUOTENAME(COLUMN_NAME) + '),1)'				      
					  WHEN DATA_TYPE IN ('datetime','datetime2') THEN 'convert(nvarchar(max),{alias}.' + QUOTENAME(COLUMN_NAME) + ',121)'				      
					  ELSE 'convert(nvarchar(max),{alias}.' + QUOTENAME(COLUMN_NAME) + ')' END  
               FROM   INFORMATION_SCHEMA.COLUMNS  
               WHERE  TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName  
                      AND COLUMNPROPERTY(  
                              OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
                              COLUMN_NAME,  
                              'ColumnID'  
                          ) = @field  

-- IF @fieldname IN ('ModifiedBy','ID')
IF @fieldname IN ('ID','N_count')
SET @excludefield=1;

		 IF @excludefield = 0  
		 BEGIN  

               SELECT @sql =   
                      '  
       insert into [pmdb].[NewAuditLog2] (      
	   AuditType,   
	   SchemaName,  
       TableName,   
	   TransactionId,  
       PK,   
       FieldName,   
       OldValue,   
       NewValue,   
       UpdateDate,  
	   UserName)  
       select ''' + @Type + ''','   
                      + '''' + @SchemaName + ''','   
                      + '''' + @TableName + ''','   
                      + '''' + @transid + ''','   
				 + @PKSelect  + ','''  
				 + @fieldname + ''''  
                      + ',' + REPLACE(@fieldvalue,'{alias}','d')   
                      + ',' + REPLACE(@fieldvalue,'{alias}','i')  
                      + ',''' + @UpdateDate + ''''  

-- + ',i.ModifiedBy'
+ ',''' + @Tablet + ''''
+ ' from #ins i ' +
+ ' full outer join #del d'
+ @PKCols
+ ' WHERE (i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
+ ' or (''' + @type + ''' IN (''I'',''D''))'
+')'
--PRINT @alenzi ;
EXEC (@alenzi )
END
END
END
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT>1
ROLLBACK;
THROW;
END CATCH