SQL Stored Procedure to Log Updates, Independent of Database Structure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Keren Ramot » SQL Stored Procedure to Log Updates,...

16 posts, Page 2 of 2

««1 2

SQL Stored Procedure to Log Updates, Independent of Database Structure

Rate Topic

Display Mode

Topic Options

Author

Message

keren ramot

Posted Tuesday, October 30, 2007 2:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 9:34 PM
Points: 3, Visits: 19

Thank you Bob. I've been feeling a little ganged up on till you and Phil Factor came along. I knew that there must be someone out there who had or will encounter similar circumstances as I have and may find this article useful. Over time I've grown skeptical of that assumption, but your feedback had really made it worth my while. I am very glad you enjoyed the article and found it useful and I appreciate you posting your elaboration on the code.
Thank you very much
K

Post #416344

Mike Elliott

Posted Friday, January 18, 2008 2:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 06, 2012 11:45 AM
Points: 124, Visits: 45

I unfortunately have ntext fields in the table(s) that need to be audited and I've been trying to find a way to get around it.

My attempt (I can't call it a solution because it doesn't work) is to try to just insert the fields that aren't ntext, text or image into a temp table. I do this by getting the names of the fields for the table being audited from the syscolumns table and put them in a variable.

My code looks like this:

CREATE TABLE #TableCache (
columnName varchar(400)
)

INSERT INTO #TableCache
SELECT '[' + sc.name + ']' as columnName
FROM sysobjects as so JOIN syscolumns as sc
ON so.id = sc.id
WHERE so.xtype = 'U' AND sc.xtype NOT IN ( 99, 35, 34) AND so.name = @TableName
AND sc.name not in ('Created', 'Modified', 'RowVersion')

SET @FieldList = ''
-- create a cursor
DECLARE tableColumn CURSOR FOR
SELECT *
FROM #TableCache

-- loop over the cursor
-- and get a list of the columns in the table that are not ntext, text or image
OPEN tableColumn
FETCH NEXT FROM tableColumn INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
if len(@FieldList) = 0
begin
SET @FieldList = '[' + @columnName + ']'
end
else
begin
SET @FieldList = @FieldList + ', [' + @ColumnName + ']'
end
-- next row, please
FETCH NEXT FROM tableColumn INTO @columnName
END

print @FieldList

-- get list of columns
Set @SQL ='SELECT ' + @FieldList + ' INTO #ins FROM inserted'
EXEC (@SQL)

The trigger errors on the last line stating invalid object inserted. (NOTE: This code is inside a trigger)

If I try to use this:
SELECT @FieldList INTO #ins FROM inserted
I get No column was specified for column 1 of '#ins'.

Any ideas?

Post #445152

hawkeyeboy

Posted Wednesday, July 30, 2008 9:44 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 16, 2011 3:27 PM
Points: 16, Visits: 130

i get Incorrect syntax near '+@VarRandom+'.

Post #543644

k.ramot

Posted Wednesday, July 30, 2008 6:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2008 6:23 PM
Points: 1, Visits: 1

I would say you’d have to be more specific then that. But before you do that, it’s possible the formatting on the page changed the code. So use the following... see how it does.

CREATE PROCEDURE dbo.SPRouterForLog
(
@SPName nvarchar(50),
@RecordId nvarchar(10),
@str1 nvarchar(4000),
@TableName nvarchar(100),
@OpName nvarchar(50),
@PageName nvarchar(50)
) AS
declare @TrriggerCreate varchar(8000)
declare @VarRandom nvarchar(50)
set @VarRandom=ltrim(str(replace(Rand(), '.', '')))
set @TrriggerCreate='CREATE TRIGGER [PreUpdateTrigger] ON dbo.'+@TableName+'
FOR UPDATE
AS
DECLARE
@iReturnCode int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@old varchar(75),
@new varchar(75),
@tmpName sysname,
@data_type nvarchar(30)
SELECT * INTO #deleted'+@VarRandom+' FROM deleted
SELECT * INTO #inserted'+@VarRandom+' FROM inserted
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(colid)
FROM syscolumns
where id = object_id('''+@TableName+''')
SELECT @iCurrentRowId = colid,
@tmpName = name
FROM syscolumns
WHERE colid = @iNextRowId
WHILE @iLoopControl = 1
BEGIN
SELECT @iNextRowId = NULL

SELECT @iNextRowId = MIN(colid)
FROM syscolumns
WHERE colid > @iCurrentRowId and id = object_id('''+@TableName+''')

if (substring (columns_updated(), 1+ round ((@iCurrentRowId - 1) / 8, 0), 1) & power (2, (@iCurrentRowId - 1) % 8) <> 0 )
begin
create table #pass'+@VarRandom+' (tempvar sysname null)
create table #pass1'+@VarRandom+' (tempvar sysname null)

set @data_type=null
select @data_type=data_type FROM information_schema.columns WHERE table_name='''+@TableName+''' and Column_Name=@tmpName
if (@data_type <> ''money'' )
Begin
exec (''insert #pass'+@VarRandom+' select d.''+@tmpName+'' from #deleted'+@VarRandom+' as d
insert #pass1'+@VarRandom+' select i.''+@tmpName+'' from #inserted'+@VarRandom+' as i'')
end
else
begin
exec (''insert #pass'+@VarRandom+' select cast(d.''+@tmpName+'' as varchar) from #deleted'+@VarRandom+' as d
insert #pass1'+@VarRandom+' select cast(i.''+@tmpName+'' as varchar) from #inserted'+@VarRandom+' as i'')
End
select @old = tempvar from #pass'+@VarRandom+'
select @new = tempvar from #pass1'+@VarRandom+'
drop table #pass1'+@VarRandom+'
drop table #pass'+@VarRandom+'
if @old is null
begin
set @old='' ''
end
if @new<>@old
begin
insert into tbl_log (TableName,RecordNumber,ActionBy,ActionPage,ChangeClmn,OldValue,NewValue,ActionDate) values ('''+@TableName+''','+@RecordId+','''+@OpName+''','''+@PageName+''',@tmpName,@old,@new,getdate())
end

end
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
SELECT @iCurrentRowId = colid,
@tmpName = name
FROM syscolumns
WHERE colid = @iNextRowId and id = object_id('''+@TableName+''')
end
drop table #deleted'+@VarRandom+'
drop table #inserted'+@VarRandom+'
'
EXEC(@TrriggerCreate)
declare @strSqlExec nvarchar(4000)
set @strSqlExec=@SPName+' '+@str1
EXEC (@strSqlExec)
drop trigger PreUpdateTrigger
GO

Post #543999

hawkeyeboy

Posted Thursday, July 31, 2008 4:03 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 16, 2011 3:27 PM
Points: 16, Visits: 130

Thank you for your reply Keren.

I believe I see the error of my ways....I was attempting to use a portion of your code within a trigger.
To be precise the section:

declare @VarRandom NVARCHAR(50)
set @VarRandom=ltrim(str(replace(Rand(), '.', '')))

SELECT * INTO #deleted'+@VarRandom+' FROM deleted
SELECT * INTO #inserted'+@VarRandom+' FROM inserted

this is where I get the syntax error. Is the above possible within a trigger?

Post #544760

tymberwyld

Posted Thursday, October 09, 2008 11:50 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268

Just to add my 2 cents. I also have been down this path using loops and what not to audit data into a generic set of tables. However, with the advent of the Service Broker, I now do all auditing asynchronously and I can handle any data type thrown at the database (including ntext, text, image, etc).

The first thing to do is setup the Audit database. This database will have one Procedure which will "receive" the incoming audit "messages" (i.e. incoming data) and will insert the incoming Xml into the generic auditing table(s).

The 2nd thing I did was I came up with a generic Trigger which could be scripted onto every table I wanted to audit. The trigger would selected all the data being updated / deleted (I didn't worry about inserts since having a CreatedDate column on all the rows would already give me that audit) and use the FOR XML clause to stuff the updated rows into Xml. In SQL 2005 the FOR XML clause has been greatly enhanced which reduced the amount of logic needed to generate a generic xml structure. Needless to say, inserting massing amounts of data into Xml is much faster than loops.

Once I had the Xml (2 xml variables, one for the "inserted" and optionally one for the "deleted" virtual tables), I passed the data off to a generic procedure which simply took the data and passed it off through the Service Broker messages I had setup. Since the SB is async, this did not slow down the OLTP data that needed to be inserted / deleted.

Please note that a SB implementation can be setup where a database can communicate with itself asynchronously instead of sending data off to another database or server instance altogether. I just choose to have a separate database so all of our application databases could be audited in a generic way without changing anything or complicated setup within each DB.

Post #583760

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions