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

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

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From	JoeM <noreply@replytogroup.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Need example showing how to log DML events for a table
Date	Tue, 31 Dec 2013 12:56:15 -0800
Organization	A noiseless patient Spider
Lines	36
Message-ID	<l9vb0r$jbd$1@dont-email.me> (permalink)
References	<l9taut$1rh$2@dont-email.me> <XnsA2A76D973EE1CYazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding	7bit
Injection-Date	Tue, 31 Dec 2013 20:55:55 +0000 (UTC)
Injection-Info	mx05.eternal-september.org; posting-host="2a280229173e55385c29211f110f0be0"; logging-data="19821"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/kqjX9G6GQZblh0bv2FsDv"
User-Agent	Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130620 Thunderbird/17.0.7
In-Reply-To	<XnsA2A76D973EE1CYazorman@127.0.0.1>
Cancel-Lock	sha1:D4S39Rql8VTuUvYpmF4bXuvdKHE=
Xref	csiph.com comp.databases.ms-sqlserver:1639

Show key headers only | 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