Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1639

Re: Need example showing how to log DML events for a table

Show all headers | View raw

On 12/31/2013 1:46 AM, Erland Sommarskog wrote:
> JoeM (noreply@replytogroup.com) writes:
>> I need to log insert, update, and delete events for a specific
>> table.  I would like the changes logged to a table, and I would
>> also want to receive an email for certain changes.
>>
>> Does anyone know of some sample SQL scripts that will create
>> the table and triggers?  Better yet would be an entire SQL
>> sample database which can be downloaded.  Thanks
>
> Audit tables and audit triggers can be designed and written in a multitude
> of ways.
>
> The simplest solution is to make the audit table a copy of the source
> table, but add columns for whom (sysname, default original_login()), when
> (datetime2(3), default sysdatetime()), from where (sysname, host_name()),
> what (sysname, app_name()) and type of change (char(1), I, U and D.) You
> would also have to add an IDENTITY column as a primary key for the audit
> table and such.
>
> In a trigger you have access to the two virtual tables "inserted" and
> "deleted". "inserted" holds the rows inserted, and for an UPDATE, the
> afterimage of the rows. "deleted" holds deleted rows and for an UPDATE, the
> before-image of the rows. Very important to keep in mind is that a trigger
> fires once per statement, so don't to the mistake of reading the contents in
> the tables into variables!
>
> For the email thing, I would probably consider a job that polls the audit
> tables and send the emails. You should be careful with putting too much
> things into a trigger, since you are inside a transaction and holding locks.
> To send mail, there is sp_dbmail_send, which is described in Books Onlines.
> (I have never used it myself.)

That's very useful information - thanks for posting that.  In 
the audit table, is there a way to include the actual DML 
(delete, or insert, or update) statement that was executed?

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Need example showing how to log DML events for a table JoeM <noreply@replytogroup.com> - 2013-12-30 18:42 -0800
  Re: Need example showing how to log DML events for a table Erland Sommarskog <esquel@sommarskog.se> - 2013-12-31 10:46 +0100
    Re: Need example showing how to log DML events for a table JoeM <noreply@replytogroup.com> - 2013-12-31 12:56 -0800
      Re: Need example showing how to log DML events for a table Erland Sommarskog <esquel@sommarskog.se> - 2013-12-31 22:03 +0100
        Re: Need example showing how to log DML events for a table JoeM <noreply@replytogroup.com> - 2013-12-31 13:33 -0800

csiph-web