In SQL 2005, if i update any value of a column in a table..... how can i get the column name(it should only return column names when any of its value is updated)

Recommended Answers

All 4 Replies

That depends on where you're trying to capture the change. You can create a trigger for update/insert/delete to compare before and after values of a transaction. Are you trying to do this at the application level to show modifications or at the database level for audit trails?

I m doin at the database level.... I am checking if there any modifications in the columns of a table. If there are any changes,
I have to retrieve the column(s) where the data is updated, old value and new value.

Help me out....

ALTER TRIGGER Emp_basic_det_update 
ON Emp_basic_det 
FOR update AS

   IF (COLUMNS_UPDATED() & 2) > 0
      BEGIN
-- Audit OLD record.
      INSERT INTO UpdatedEmpInfo
         (EID,
         OldEName,
		 NewEName,
         curr_Dat_Time,
		 Field_Name)
         SELECT  
            del.EID,
            del.EName,
			ins.EName,
			getdate(),
                                               {??????????????}
			         FROM deleted del,inserted ins 
   END
GO

The above is my Trigger statement.
In that there is one column Field_Name. Whenever i update any column i should get the column name in that(what should come in {?????????????}).
One more thing, oldEName and NewEName are assigned by static values but even that has to be dynamic.(As and when column value changes, depending on that it has to get assigned)

Help me out.......

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.