April 7, 2004 at 8:36 am
Below is a trigger that I'm TRYING to use (in SQL2000) to list which columns are updated. However, it never stops running and I can't determine why. Apparently the WHILE maybe generating an infinite loop. To test, just change the table name 'admin' in a few places. Any help would be appreciated.
------------------------------------------------------------------
CREATE Trigger tr_admin
-- list all columns that were changed
On dbo.admin
For Insert, Update
As
SET NOCOUNT OFF
declare @intCountColumn int,
@intColumn int
-- count columns in the table
SELECT @intCountColumn = Count(Ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'admin'
select COLUMNS_UPDATED() "COLUMNS UPDATED"
Set @intColumn = 1
-- loop through columns
while @intColumn <= @intCountColumn
begin
if COLUMNS_UPDATED() & @intColumn = @intColumn
Print 'Column (' + Cast(@intColumn as varchar) + ') ' +
COL_NAME(object_id('admin'), @intColumn) + ' has been changed!'
Set @intColumn = @intColumn + 1
End
PRINT @INTCOLUMN
GO
smv929
April 8, 2004 at 1:10 am
if admin table has 8 column or less
create Trigger tr_admin
-- list all columns that were changed
On admin
For Update
As
SET NOCOUNT OFF
declare @intCountColumn int
-- count columns in the table
SELECT @intCountColumn = Count(Ordinal_position)-1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'admin'
-- list column names
while @intCountColumn >= 0
BEGIN
if (COLUMNS_UPDATED() & POWER(2,(@intCountColumn)) > 0)
Print 'Column (' + Cast(@intCountColumn+1 as varchar) + ') ' + COL_NAME(object_id('admin'), @intCountColumn+1) + ' has been changed!'
Set @intCountColumn = @intCountColumn - 1
End
GO
April 8, 2004 at 1:45 am
Table with any number of columns
create Trigger tr_admin
-- list all columns that were changed
On admin
For Update
As
SET NOCOUNT OFF
declare @intCountColumn int, @n int
-- count columns in the table
SELECT @intCountColumn = Count(Ordinal_position)-1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'admin'
-- list column names
while @intCountColumn >= 0
begin
set @n = ceiling((@intCountColumn+1) / 8.0)
if substring(COLUMNS_UPDATED(), @n, 1) & power(2, (@intCountColumn-8*(@n-1))) > 0
Print 'Column (' + Cast(@intCountColumn+1 as varchar) + ') ' + COL_NAME(object_id('admin'), @intCountColumn+1) + ' has been changed!'
Set @intCountColumn = @intCountColumn - 1
End
GO
select ceiling(9/8.0)
select col_name(object_id('admin'),9)
SELECT Count(Ordinal_position)-1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'admin'
April 8, 2004 at 5:38 am
No hard-coded table name in trigger and works for any number of columns.
Create Trigger tr_admin
-- list all columns that were changed
On admin
For Update
As
SET NOCOUNT On
declare @intCountColumn int, @n int, @tblname sysname
select @tblname = object_name(parent_obj) from sysobjects where name = object_name(@@procid)
-- count columns in the table
SELECT @intCountColumn = Count(Ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE TABLE_NAME = @tblname
-- list column names
while @intCountColumn > 0
begin
set @n = ceiling(@intCountColumn / 8.0)
if substring(COLUMNS_UPDATED(), @n, 1) & power(2, (@intCountColumn-8*(@n-1)-1)) > 0
select @tblname+'.' + COL_NAME(object_id(@tblname), @intCountColumn) + ' has been changed!'
Set @intCountColumn = @intCountColumn - 1
End
GO
April 8, 2004 at 7:12 am
Wz700, thanks! The COLUMNS_UPDATED() conversion part was my problem. I'm new to working with that and couldn't find any good examples. I'm surprised that SQL Server doesn't have a more friendlier function to return the column. I will modify it to write to a log table and include the before and after values if they differ. Thanks agian.
smv929
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply