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

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

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

Show all headers | View raw

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

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