Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Unanswered: Audit triggers problem

    Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.

    The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
    Not all of the tables are updated and data cannot be completely inserted.

    This is the trigger i have been using - could someone tell me how to modify it to work.



    /*
    This trigger audit trails all changes made to a table.
    It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
    It will put out an error message if there is no primary key on the table
    You will need to change @TableName to match the table to be audit trailed
    */

    ALTER trigger tr_TableName
    on dbo.TableName for insert, update, delete
    as

    declare @bit int ,
    @field int ,
    @maxfield int ,
    @char int ,
    @fieldname varchar(128) ,
    @TableName varchar(128) ,
    @PKCols varchar(1000) ,
    @sql varchar(2000),
    @UpdateDate varchar(21) ,
    @Action nvarchar(50) ,
    @HostName nvarchar(50),
    @PKFieldName varchar (1000)


    IF EXISTS(SELECT * FROM inserted)
    IF EXISTS(SELECT * FROM deleted)
    --update = inserted and deleted tables both contain data
    BEGIN
    SET @Action = 'UPDATE'
    SELECT @DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
    END
    ELSE

    --insert = inserted contains data, deleted does not
    BEGIN
    SET @Action = 'INSERT'
    select @DeviceID = (SELECT DeviceID from inserted)
    END
    ELSE
    --delete = deleted contains data, inserted does not
    BEGIN
    SET @Action = 'DELETE'
    select @DeviceID = (SELECT DeviceID from deleted)
    END

    select @TableName = 'TableName'

    -- date
    select @HostName = host_name(),
    @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
    --@DeviceID,
    @PKFieldName=(select top 1 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)

    -- 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_NAME = @TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    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(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
    begin
    select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
    select @sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
    -- select @sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
    select @sql = @sql + ' select ''' + @TableName + ''''
    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 + ',''' + @Action + ''''
    select @sql = @sql + ',''' + @HostName + ''''
    select @sql = @sql + ',''' + @PKFieldName + ''''
    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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You have to remember that inerted and deleted can have a set of data...not just 1 row...

    Tracking chnages should br pretty straighty forward...

    If the data is altered just move the whole row to history...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    7
    Quote Originally Posted by Brett Kaiser
    You have to remember that inerted and deleted can have a set of data...not just 1 row...

    Tracking chnages should br pretty straighty forward...

    If the data is altered just move the whole row to history...

    How do 'move the whole row to history' - im not sure.

    The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
    CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
    GO
    
    CREATE TRIGGER myTrigger99 ON myTable99
    FOR UPDATE, DELETE
    AS
    	INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
    	SELECT Col1, Col2, ModifiedDate FROM deleted
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'x'
    GO
    SELECT * FROM myTable99
    SELECT * FROM myAudit99
    GO
    
    UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
    GO
    SELECT * FROM myTable99
    SELECT * FROM myAudit99
    GO
    
    UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
    GO
    SELECT * FROM myTable99
    SELECT * FROM myAudit99
    GO
    
    DELETE FROM myTable99 WHERE Col1 = 1
    GO
    SELECT * FROM myTable99
    SELECT * FROM myAudit99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    DROP TABLE myAudit99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •