find modified column name 1

[kunai]

Hi,

Is there a way to find which table column was modified?
I'd like use that information to create an audit table.

e.g. Let's say i have a table with col1, col2, col3.

I'd like to use an update trigger to insert into my audit table: date, user, colName, old value, new value

I need to find which column was just updated to put in colName.

Is there a system table where i get that information?

Thanks for any suggestions.

 

[Catadmin]

SysObjects & SysColumns, though I would recommend checking out Information_Schema_Views in Books Online and using those so you don't inadvertantly change data in the Sys tables..

That would be bad.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[kunai]

I've been checking out Information_Schema.Columns and Information_Schema.Views, i don't see how it gives me information on which column was just modified.

 

[maswien]

Assume you have column col1, col2, col3 in mytable and row_id is primary key, then create following trigger:

CREATE TRIGGER updit
ON myTable
FOR update AS
IF (COLUMNS_UPDATED() & 1) > 0
BEGIN
   INSERT INTO audit (m_date, l_user, colName, old_value, new_value)
         SELECT getdate(), suser_sname(), 'col1', del.col1, ins.col1
         FROM deleted del inner join inserted ins on del.row_id = ins.row_id
end

IF (COLUMNS_UPDATED() & 2) > 0
BEGIN
   INSERT INTO audit (m_date, l_user, colName, old_value, new_value)
         SELECT getdate(), suser_sname(), 'col2', del.col2, ins.col2
         FROM deleted del inner join inserted ins on del.row_id = ins.row_id
end

IF (COLUMNS_UPDATED() & 4) > 0
BEGIN
   INSERT INTO audit (m_date, l_user, colName, old_value, new_value)
         SELECT getdate(), suser_sname(), 'col3', del.col3, ins.col3
         FROM deleted del inner join inserted ins on del.row_id = ins.row_id
end

GO

 

[kunai]

ah, COLUMNS_UPDATED(), interesting. Sound promising.
I'll look into this later.

Thanks!