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 Vazquezjv (julie_vazquez@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||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)|||jv (julie_vazquez@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment