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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy