1

I have price table that has over 100 columns. I'm in the process of fine tunning triggers and reducing amounts of records inserted, to audit changes when updates are applied.

The audit table structure is audit_hdr and Audit_price for example (same columns as Price + 5 extra columns for auditing purposes) - there are several tables (cost, locations, etc.)... and the triggers are all created dynamically through one script.

It's not a problem when inserting a record in the audit price table, since I'm checking if columns have been updated with update(column name) statements and verify if there are differences between inseted and deleted values (for all the coluns) before deciding to insert into the audit_price table...

Where it gets tricky is when I need to update the audit_hdr table. To decide if I need to insert a record into the audit_hdr table, I need to make sure I've inserted something in the audit_price table.

Here's the structure:

-- price
    CREATE TABLE [dbo].[price](
    [SKU] [varchar](13) NOT NULL,
    [targ] [varchar](5) NOT NULL,
    [PLevel] [int] NOT NULL,
    [price] [money] NULL,
    [Qty] [float] NULL,
    [discountType] [varchar](1) NULL,
    [discountAmnt] [money] NULL,
    [discountPerc] [float] NULL,
    [SaleQty] [float] NULL,
    [SaleAmnt] [money] NULL,
 CONSTRAINT [PK_price] PRIMARY KEY CLUSTERED 
(
    [SKU] ASC, [Targ] ASC, [PLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- audit_price
CREATE TABLE [dbo].[audit_price](
    [SKU] [varchar](13) NOT NULL,
    [Targ] [varchar](5) NOT NULL,
    [PLevel] [int] NOT NULL,
    [price] [money] NULL,
    [Qty] [float] NULL,
    [discountType] [varchar](1) NULL,
    [discountAmnt] [money] NULL,
    [discountPerc] [float] NULL,
    [SaleQty] [float] NULL,
    [SaleAmnt] [money] NULL,
    [trig_by] [varchar](100) NULL,
    [Operation_type] [int] NULL,
    [user] [varchar](128) NULL,
    [modified_date] [datetime] NOT NULL,
    [audit_hdr_id] [int] NOT NULL,
    [columns_updated] [varchar](max) NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [audit_Price_X1] ON [dbo].[Audit_price]
(
    [modified_date] ASC,
    [Operation_type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- Audit_hdr
CREATE TABLE [dbo].[AUDIT_HDR](
    [modified_date] [datetime] NOT NULL,
    [Operation_type] [int] NOT NULL,
    [TableName] [sysname] NOT NULL,
    [user] [sysname] NOT NULL,
    [trig_by] [varchar](100) NULL,
    [ID] [int] NOT NULL,
 CONSTRAINT [audit_HDR_P01] PRIMARY KEY CLUSTERED 
(
    [modified_date] ASC,
    [TableName] ASC,
    [user] ASC,
    [Operation_type] ASC,
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Following bellow is the code of my dynamic script attempting to create the trigger... I know why it doesn't work... what I'm looking for is how would it be possible to mix and update() statement with the verification of changes on each field (between deleted and Inserted)

    DECLARE @ColList VARCHAR(MAX)
DECLARE @InsertColList VARCHAR(MAX)
DECLARE @UpdateCheck VARCHAR(MAX)
DECLARE @updateFields VARCHAR(MAX)
DECLARE @updateFieldsToDeploy VARCHAR(MAX)
DECLARE @F1001_EXISTS INT=0
DECLARE @pk_columns VARCHAR(MAX)
SELECT @ColList = '',@UpdateCheck = ' ',@InsertColList = '', @updateFields='',@updateFieldsToDeploy='', @pk_columns=''
SELECT @ColList = @ColList +
    CASE SC.is_identity  
    WHEN 1 THEN 'CONVERT(' + ST.name + ',' + SC.name + ') as ' + SC.name
        ELSE SC.name  
    END + ','
    , @InsertColList = @InsertColList + SC.name + ','
    , @UpdateCheck = @UpdateCheck + 'CASE WHEN UPDATE(' + SC.name + ') AND (INS.'+SC.name+'<>DEL.'+SC.name+' OR (DEL.'+SC.name+' IS NULL AND INS.'+SC.name+' IS NOT NULL)) THEN ''' + SC.name + '-'' ELSE '''' END +' + CHAR(10) 
    , @updateFields= @updateFields + 'UPDATE(' + SC.name + ') AND '
    , @updateFieldsToDeploy=@updateFieldsToDeploy + CASE WHEN SC.name NOT IN ('F902','F1001','F253','F2119','F1805','F2569','F2570','F2571','F2572','F2573','F2574','F2575','F2576','F2577','F2578','F2579','F2580') THEN 'UPDATE(' + SC.name + ') OR ' ELSE '' END
    , @pk_columns= @pk_columns + CASE WHEN PK.COLUMN_NAME IS NULL THEN '' ELSE 'DEL.'+PK.COLUMN_NAME+'=INS.'+PK.COLUMN_NAME+' AND ' END 
FROM SYS.COLUMNS SC  
    JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id     
    JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id  
    JOIN SYS.types ST   ON ST.user_type_id = SC.user_type_id AND ST.system_type_id = SC.system_type_id   
    LEFT JOIN (SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
            WHERE tc.CONSTRAINT_TYPE = 'Primary Key' AND TC.TABLE_NAME='price') PK ON PK.COLUMN_NAME=SC.NAME
    WHERE SO.name  = 'price'
    AND UPPER(ST.name)  <> UPPER('timestamp')


SELECT  @ColList=SUBSTRING(@ColList,1,CHARINDEX(',',@ColList,1)-1), -- AS F2846,
    @InsertColList=SUBSTRING(@InsertColList,1,LEN(@InsertColList)-1), -- AS F1849, 
    @UpdateCheck=SUBSTRING(@UpdateCheck,1,LEN(@UpdateCheck)-2), -- AS F2835, 
    @updateFields=SUBSTRING(@updateFields,1,LEN(@updateFields)-3) + CASE WHEN @F1001_EXISTS=1 THEN ' OR (UPDATE(F1001) AND (SELECT COUNT(*) FROM INSERTED INS JOIN DELETED DEL ON ' + SUBSTRING(@pk_columns,1,len(@pk_columns)-4) + ' WHERE DEL.F1001=0 AND INS.F1001=1)>0)' else '' END, -- AS F2910, 
    @updateFieldsToDeploy=SUBSTRING(@updateFieldsToDeploy,1,LEN(@updateFieldsToDeploy)-3) + CASE WHEN @F1001_EXISTS=1 THEN ' OR (UPDATE(F1001) AND (SELECT COUNT(*) FROM INSERTED INS JOIN DELETED DEL ON ' + SUBSTRING(@pk_columns,1,len(@pk_columns)-4) + ' WHERE DEL.F1001=0 AND INS.F1001=1)>0)' ELSE '' END, -- AS F2912,
    @pk_columns=SUBSTRING(@pk_columns,1,len(@pk_columns)-4) -- AS F3008

/* UPDATE */
DECLARE @TABLENAME VARCHAR(20)='PRICE'
DECLARE @TRIGNAME VARCHAR(20)='AUDIT_PRICE_TRIG_U'
DECLARE @SQLStr VARCHAR(MAX)=''
IF OBJECT_ID(@TRIGNAME) IS NOT NULL 
    Begin 
        SET @SQLStr = 'DROP TRIGGER ' + @TRIGNAME 
        exec(@SQLStr)
        SET @SQLStr = ''
    End
SET @SQLStr = @SQLStr + '
CREATE TRIGGER ' + @TRIGNAME + '
ON ' + @TABLENAME + '
FOR UPDATE
AS
DECLARE @SystemDate datetime2, 
    @SystemDateUT datetime2, 
    @TrigBy varchar(100), 
    @AuditHdrSeq bigint,
    @user varchar(20)=''MyTestUser''

IF NOT EXISTS(SELECT 1 FROM INSERTED) AND NOT EXISTS(SELECT 1 FROM DELETED) RETURN

SET @User=CASE WHEN @User='''' THEN SUSER_SNAME() ELSE @User END
SELECT @SystemDate = getdate(),
    @SystemDateUT = GETUTCDATE(), 
    @TrigBy = ''' + @TRIGNAME + '''
IF ' + @updateFields + '
BEGIN
    /*
    CREATE SEQUENCE NUMBER
    */
    SELECT @AuditHdrSeq = ID
        FROM Audit_HDR
        WHERE Modified_date = @SystemDateUT AND TableName = ''' + @TRIGNAME + ''' AND user = @user AND Operation_type = 2
    IF @AuditHdrSeq IS NULL
    BEGIN
        SET @AuditHdrSeq=NEXT VALUE FOR REV_HDR_F3001       
        INSERT INTO Audit_HDR ([ID],[modified_date],[Operation_type],[TableName],[user],[trig_by]) VALUES (@AuditHdrSeq,@SystemDateUT,2, ''' + @TABLENAME + ''',@User,''' + @TRIGNAME + ''')
    END
    /*
    RECORD CHANGES
    */
    INSERT INTO dbo.Audit_' + @TABLENAME + '
    (' + @InsertColList + ',[trig_by],[Operation_type],[user],[modified_date],[audit_hdr_id],[columns_updated])
    SELECT INS.' + replace(@InsertColList,',',',INS.') + ',''' + @TRIGNAME + ''',2,@User,@SystemDateUT,@AuditHdrSeq, ' + @UpdateCheck + '
    FROM INSERTED INS
    LEFT JOIN DELETED DEL ON ' + @pk_columns + '
END;'

--  Select @SQLStr 
 exec (@SQLStr)

Note where @updateFields is used in the Dynamic SQL is where I need to do update(columnName) and ins.ColumnName<>Del.Column_name

But Ihave tables that have hundreds of columns and wouldn't want to hundreds of Selects from inserted for each column to compare with hundreds of Selects from deleted for each column...

I've been searching for a better solution and to my surprise, I haven't found anything to help.

  • If you were on SQL Server 2016, Temporal tables probably would solve this problem for you. CDC is another way to create an audit trail vs triggers. CT would miss changes in-between. – Shaulinator Nov 12 at 21:31
  • @Shaulinator Our customers can have SQL 2008 (Slowly fading out) Express to SQL 2019 enterprise... and by memory, CDC is only on Enterprise... Right? – JohnG Nov 12 at 21:39
  • CDC is an enterprise feature in 2008. In 2016 SP1 it is in standard, developer, and enterprise. – Shaulinator Nov 12 at 21:42
  • @Shaulinator Our software needs to run on express too. :-( – JohnG Nov 12 at 21:43
  • Unfortunately CDC does not work on express or web. :( – Shaulinator Nov 12 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.