SQL Server Performance Forum – Threads Archive

Track each field update

Our company table is having more than 45 fields like companyid, comapnyname, address1, address2, pobox, city, state, country etc.. etc..
We want to know for each field for each company from whom (user) the value came from as well as when it was updated. For this we have created one additional table, which is having fields like seqid, companyid, fieldname, oldvalue, userid, dateadd
My question is while executing the update statement, how to track each field whether it is updated or not to insert into new table?
Surendra Kalekar

---

You need an UPDATE trigger and an audit table. Then you can utilize then inserted and deleted pseudotables. Search the forum. There have been many similar request in the past. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Books Online:
create trigger + UPDATE(column) or COLUMNS_UPDATED() WBR, Vlad A. Scherbinin

---

Thanks Frank, Can you elaborate the meaning of audit table?
Surendra Kalekar

---

Execute an update/insert trigger in the Table 1 which is having more than 45 fields. Triggers are executed as soon as the values are inserted into the table 1. N.Babu Godson Samuel

---

The audit table is usually identical in structure to the table you wish to audit. There are usually 2 or more extra columns as for who did the change and when the change was done. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Hi Surendra,<br /><br />for example you have a table "tablea" and similar structured table "tracedvalue"(+3 more column :TypeofAction,HostName,Sysdate)<br /> <br />then create a trigger on table "tablea"<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><b><br />SET QUOTED_IDENTIFIER ON <br />GO<br />SET ANSI_NULLS ON <br />GO<br />SET NOCOUNT ON<br />go<br />create trigger UpdTrc <br />on db.dbo.tablea<br />for update <br />as <br />[email protected] varchar(12)<br />[email protected]=host_name()<br />[email protected]=’hostname’ — compare it if you want to audit this for some part clients<br />[email protected]=’dev3′<br />begin<br />insert into db.dbo.tracedvalues <br />select inserted.*, <br />’U’,getdate(),host_name() from inserted <br />end<br />GO<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br /></b><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />HTH<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />

---

Thanks everybody.
Surendra Kalekar

---

]]>