From: esquel@sommarskog.se   
      
   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.)   
      
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)