question

KaiYang-6525 avatar image
0 Votes"
KaiYang-6525 asked KaiYang-6525 commented

trigger to record delete active

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'
                           + ',''' + @Username + ''''
                           + ' 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 @sql;
                    EXEC (@sql)
                 END
                END
            END
         COMMIT
     END TRY
     BEGIN CATCH
         IF @@TRANCOUNT>1
             ROLLBACK;
         THROW;
     END CATCH
    






sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i uncomment the code --PRINT @sql; but I can not see the code in message window of SSMS

0 Votes 0 ·

does the problem happen at:

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

          IF @excludefield = 0
          BEGIN

Because delete action will affected entire row, including ID and N_count columns, so the code can not generate @sql string to insert the result into log table?

0 Votes 0 ·

0 Answers