By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Iterating updated fields within a Trigger

P: n/a
jv
Hi,
I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?
Thanks in advance,
Julie Vazquez

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
jv (ju***********@hotmail.com) writes:
I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?


Not in a way that I would call painless. You could use something
with dynamic SQL, but that would come with a performance cost, and
you really don't want to spend to much time in triggers, since you
are in a transaction. You can easliy get into locking issues.

It is better - although boring - to type the SQL for each column to
log. Writing a program that generates the trigger code could be an
option to save time.

If you are in for massive auditing, consider using a third-party
product. A trigger-based solution is SQLAudit from Red Matrix.
Lumigent offers Entegra which works from the transaction log.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
jv
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.

Thanks.

Jul 23 '05 #3

P: n/a
On 17 Jan 2005 17:02:45 -0800, jv wrote:
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.

Thanks.


Hi jv,

Check out the INFORMATION_SCHEMA.COLUMNS view.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

P: n/a
jv (ju***********@hotmail.com) writes:
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.


Yes. But don't do it. If you are going roll your own, write code for
each column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.