Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Stored Procedure to Log Updates, Independent of Database Structure


SQL Stored Procedure to Log Updates, Independent of Database Structure

Author
Message
keren ramot
keren ramot
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
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
Mike Elliott
Mike Elliott
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 54
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?
hawkeyeboy
hawkeyeboy
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 130
i get Incorrect syntax near '+@VarRandom+'.
k.ramot
k.ramot
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
hawkeyeboy
hawkeyeboy
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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?
tymberwyld
tymberwyld
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 275
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search