sql server programming:
David, This is not a good strategy for auditing. It makes reverting a row back to its prior state an exercise in tedium, rather than a simple update. Why not just use ordinary columns? James Hokes [quoted text, click to view] "David" <auto87829@hushmail.com> wrote in message
news:3ffa983d$0$18751$afc38c87@news.optusnet.com.au...
> I've added an audit table to an existing application, each row of which
> contains:
>
> the name of the user that made the modification,
> the time,
> the table that's affected,
> insert, update or delete,
> the old row as a comma-delimited string
>
> I'm a bit unhappy with the guts of the trigger that populates the table:
>
> insert my_audit_log ( table_name, audit_action, audit_name, audit_date,
> old_data )
> select 'foobar', 'D', user_name(), getdate(),
> convert(varchar(10), deleted.foo_id ) + ',' +
> convert(varchar(10), deleted.bar_id ) + ',' +
> deleted.last_name + ',' +
> deleted.first_name + ',' +
> deleted.phone
> from deleted
>
> There are many tables to audit ... Is there a generic way to convert the
> contents of a row of the deleted table into a comma-delimited varchar
> without referencing each column?
>
> Cheers,
>
> David
>
>
I think this is the way to go forward ... There is nothing inbuild to form a comma separated string from a row as far as I know. -- HTH, Vinod Kumar MCSE, DBA, MCAD, MCSD http://www.extremeexperts.com Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp [quoted text, click to view] "David" <auto87829@hushmail.com> wrote in message
news:3ffa983d$0$18751$afc38c87@news.optusnet.com.au...
> I've added an audit table to an existing application, each row of which
> contains:
>
> the name of the user that made the modification,
> the time,
> the table that's affected,
> insert, update or delete,
> the old row as a comma-delimited string
>
> I'm a bit unhappy with the guts of the trigger that populates the table:
>
> insert my_audit_log ( table_name, audit_action, audit_name, audit_date,
> old_data )
> select 'foobar', 'D', user_name(), getdate(),
> convert(varchar(10), deleted.foo_id ) + ',' +
> convert(varchar(10), deleted.bar_id ) + ',' +
> deleted.last_name + ',' +
> deleted.first_name + ',' +
> deleted.phone
> from deleted
>
> There are many tables to audit ... Is there a generic way to convert the
> contents of a row of the deleted table into a comma-delimited varchar
> without referencing each column?
>
> Cheers,
>
> David
>
>
I've added an audit table to an existing application, each row of which
contains:
the name of the user that made the modification,
the time,
the table that's affected,
insert, update or delete,
the old row as a comma-delimited string
I'm a bit unhappy with the guts of the trigger that populates the table:
insert my_audit_log ( table_name, audit_action, audit_name, audit_date,
old_data )
select 'foobar', 'D', user_name(), getdate(),
convert(varchar(10), deleted.foo_id ) + ',' +
convert(varchar(10), deleted.bar_id ) + ',' +
deleted.last_name + ',' +
deleted.first_name + ',' +
deleted.phone
from deleted
There are many tables to audit ... Is there a generic way to convert the
contents of a row of the deleted table into a comma-delimited varchar
without referencing each column?
Cheers,
David
Don't see what you're looking for? Try a search.
|
