0

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)

2
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by shreyas2785
0

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?

0

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....

0
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.......

Edited by Ezzaral: Added code tags. Please use them to format all code that you post.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.