CREATE TRIGGER my_trig
ON my_table
FOR INSERT, UPDATE
AS

DECLARE @nocol int, @bykcol int, @pangkat int, @bit int, @start int, @bagi int  

set @start = 1 
SET @bykcol = (SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table')
set @bagi = (select ((@bykcol/8) + 1) )

while @start <= @bagi
begin

      SET @nocol = 1

      WHILE @nocol <= 8 
      begin   

         SET @bit = @nocol - 1 
         SET @pangkat = (SELECT POWER(2,@bit))

         IF  (substring(columns_updated(),@start,1) = @pangkat)

	  BEGIN
 
	  PRINT SUSER_SNAME()
	  PRINT HOST_NAME()
	  PRINT GETDATE()
	  PRINT COL_NAME(OBJECT_ID('my_table'),@nocol)

     	  END

 	  SET @nocol = @nocol + 1
      end
   set @start = @start + 1
end

quote:

---

Djohan writes "Hi, I have some problem for creating trigger. I have created trigger to get history : who is update the table, which workstation, times etc. the result is just for few columns(8). I get difficulties for a table which have more than 8 columns. Here the script. can you help me ?
CREATE TABLE my_table
(a int NOT NULL,
b int NULL,
c int NULL,
d int NULL,
e int NULL,
f int NULL,
g bit NULL default 0,
h CHAR(2) NULL,
i VARCHAR(50) NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT, UPDATE
AS

DECLARE @nocol int, @bykcol int, @pangkat int, @bit int, @start int, @bagi int  

set @start = 1 
SET @bykcol = (SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table')
set @bagi = (select ((@bykcol/8) + 1) )

while @start <= @bagi
begin

      SET @nocol = 1

      WHILE @nocol <= 8 
      begin   

         SET @bit = @nocol - 1 
         SET @pangkat = (SELECT POWER(2,@bit))

         IF  (substring(columns_updated(),@start,1) = @pangkat)

	  BEGIN
 
	  PRINT SUSER_SNAME()
	  PRINT HOST_NAME()
	  PRINT GETDATE()
	  PRINT COL_NAME(OBJECT_ID('my_table'),@nocol)

     	  END

 	  SET @nocol = @nocol + 1
      end
   set @start = @start + 1
end

GO"

---