From: noreply@replytogroup.com   
      
   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?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)