September 23, 2004 at 1:40 pm
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.
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.
/*
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
September 23, 2004 at 2:52 pm
Hi,
Seems to me you're almost there. If I understand this correctly, you will always have a column called "DeviceID", that uniquely identifies a row in all tables? If this is the case, I suggest you try to do two changes:
1. Remove all the codelines that try to do an assignment to @deviceID. Using a single-value variable will only work for single inserts / updates / deletes, and gets you in trouble when the actions are set-based.
2. Add another line to the building of the insert-statement, below @PKFieldName:
select @sql = @sql + ',''' + isnull(i.deviceID, d.deviceID) + ''''
I hope this helps - worth a try anyway.
September 26, 2004 at 1:07 pm
Oops, Actually I posted the wrong trigger, this is the actual code.
This is the trigger code, i just change where it says Tablename to the different names of my tables. The problem is that it works fine when on 2 tables but any more and the triggers don't work. I think it may be because there are more than one row being updated at once when my inventory agent writes all of its data to the different tables. So how do i solve this locking/concurrency/access problem
create trigger tr_Tablename on 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) ,
@UserName varchar(128) ,
@Action nvarchar(50) ,
@HostName nvarchar(50)
IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
SET @Action = 'UPDATE'
ELSE
--insert = inserted contains data, deleted does not
SET @Action = 'INSERT'
ELSE
--delete = deleted contains data, inserted does not
SET @Action = 'DELETE'
select @TableName = 'Tablename'
-- date and user
select @UserName = dbo.getaudituser(),
@HostName = host_name(),
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
-- 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 Audit (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, Action, Host)'
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 + ',''' + @UserName + ''''
select @sql = @sql + ',''' + @Action + ''''
select @sql = @sql + ',''' + @HostName + ''''
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
September 26, 2004 at 1:14 pm
Do you get an error message at any point, or is it based on the data quality that you're able to say that something has gone wrong?
My first suggestion would be to skip the usage of #ins and #del, and just use the sets "inserted" and "deleted". But that is just a hunch - I don't really see any way they can cause concurrency problems.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply