How to dynamically track changes in Tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to dynamically track changes in Tables?

Author

Topic

HugoHiasl
Starting Member

4 Posts

Posted - 2004-08-09 : 07:56:44

Hi,

I'm trying to get a simple problem to work.
I have a few tables for which I want to track every change.

It's easy to set a trigger which compares the field value in the "inserted" table against the field value in the "deleted" table.

But the problem is, I have to set up each comparison manually for each field.

I'd like to do it generic. A loop, which steps through all fields an checks if the values are different and then write the log-entry into a different table.

Is there any way to do this with SQL Server?

Any suggestion is very welcome.

Thanks in Advance
Oliver

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 08:19:55

if i understand correctly:

there is a

SELECT column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'

which will give you column names, and you could do a little dynamic sql...

Go with the flow & have fun! Else fight the flow :)

HugoHiasl
Starting Member

4 Posts

Posted - 2004-08-09 : 08:28:03

Thanks for the fast reply.

I tried this before. the problem is, if I do something like this:

--snipp--
declare myCursor cursor local for
select sc.name from syscolumns sc
inner join sysobjects sob on sc.id = sob.id
where lower(sob.name) = 't_oe_neededinfos'

open myCursor
fetch next from myCursor into @colName

while @@fetch_status = 0

begin
set @execDel = 'SELECT ' + @colName + ' FROM DELETED'
EXEC(@execDel)
set @execIns = 'SELECT ' + @colName + ' FROM INSERTED'
EXEC(@execIns)

if @execDel <> @execIns
begin
insert into t_oe_test(changed) values(@colName)
end
fetch next from myCursor into @colName
end
--snipp--

I get the error message, that DELETED is not a valid object.
The EXEC call seems to be executed outside of the trigger context.

ruan4u
Posting Yak Master

132 Posts

Posted - 2004-08-09 : 08:32:29

assuming that ur not writing this code in insert trigger?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 08:46:19

you don't need a cursor for creating statements:

SELECT 'exec (SELECT ' + column_name +' FROM DELETED)'
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'table'

maybe your trigger isn't defnined for delete statements?

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 08:48:58

from Execute in BOL
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* A @@FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor

i don't see how exe is ran differently...

Go with the flow & have fun! Else fight the flow :)

HugoHiasl
Starting Member

4 Posts

Posted - 2004-08-09 : 16:10:06

After trying different approaches, it seems not to be possible to do what I need.

Thanks to all which replied to this task. If you are interested in my problem, you can try it by yourself very easy.

Create a little Table like
CREATE T_TEST (
field1 varchar(100),
field2 varchar(100))

then try to create a trigger for update, which prints the name of the column, which has changed, by dynamically looping through the columns without using the names of the columns. As posted before my first approach was this:

--snipp--
declare myCursor cursor local for
select sc.name from syscolumns sc
inner join sysobjects sob on sc.id = sob.id
where lower(sob.name) = 't_oe_neededinfos'

open myCursor
fetch next from myCursor into @colName

while @@fetch_status = 0

begin
set @execDel = 'SELECT ' + @colName + ' FROM DELETED'
EXEC(@execDel)
set @execIns = 'SELECT ' + @colName + ' FROM INSERTED'
EXEC(@execIns)

if @execDel <> @execIns
begin
insert into t_oe_test(changed) values(@colName)
end
fetch next from myCursor into @colName
end
--snipp--

it is in a "for update" trigger. The problem is, that in this approach the exec(@execDel) and exec(@execIns) is outside the scope of the temporary local tables INSERTED or DELETED.

You can easily check this by creating this trigger.

But I was not able to solve my problem. A working possibility could be a global temporary table. But I wanted to avoid this.

Thanks for all your input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources