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 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: References: 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: Cancel-Lock: sha1:D4S39Rql8VTuUvYpmF4bXuvdKHE= Xref: csiph.com comp.databases.ms-sqlserver:1639 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?