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

Groups > comp.databases.ms-sqlserver > #1637 > unrolled thread

Need example showing how to log DML events for a table

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1637 — Need example showing how to log DML events for a table

I need to modify my SQL 2008 R2 database as follows:

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

[toc] | [next] | [standalone]

#1638

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

[toc] | [prev] | [next] | [standalone]

#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?

[toc] | [prev] | [next] | [standalone]

#1640

JoeM (noreply@replytogroup.com) writes:
> 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?
> 

Yes and no. You can use the command DBCC INPUTBUFFER for this purpose, but

1) It requires high permissions, even for your own spid. This can be 
addressed with certificate signing, but given the next points, I don't think
it's worth the hassle.

2) If the DML statement was executed inside a stored procedure, you will 
only see the call to the stored procedure.

3) If the application uses parameterised statements (which it should), you 
will only see the parameter names.



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1641

On 12/31/2013 1:03 PM, Erland Sommarskog wrote:
> Yes and no. You can use the command DBCC INPUTBUFFER for this purpose, but
>
> 1) It requires high permissions, even for your own spid. This can be
> addressed with certificate signing, but given the next points, I don't think
> it's worth the hassle.
>
> 2) If the DML statement was executed inside a stored procedure, you will
> only see the call to the stored procedure.
>
> 3) If the application uses parameterised statements (which it should), you
> will only see the parameter names.

Thanks much.  I'll just keep it simple then.  Know of any good 
examples showing how to set it up?

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web