Creating trigger for audit

• Martin Balbo

  SSC Eights!

  Points: 874

  More actions

  May 19, 2006 at 8:39 am

  #115207

  Hi friends...

  I want to create a trigger for audit insert, update, and delete operativos on some tables, but in addition, i want to audit the sql sentences which is running at this specific moment, anyone can tell me how can i get this?

  the code is the following

  CREATE TRIGGER Audit

      ON dbo.prueba

      FOR DELETE

  AS 

        INSERT Auditoria

          (Fecha, UserName, Operacion)

          SELECT Getdate(), SUSER_SNAME(), 'Delete'

  i want to insert in the auditoria table the sentence which is deleting the current row(s).

  Thanks for help

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  May 19, 2006 at 9:01 am

  #639026

  you'd need fn_getsql or dbcc inputbuffer.

  Make sure you understand the implications of placing triggers on a table - if your database is transactional you may find you take a serious performance hit. You will also find you lose in place updates when there is an update trigger.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Khwaja Arshaduddin

  SSC Eights!

  Points: 843

  More actions

  May 22, 2006 at 3:19 am

  #639177

  I think you can try with rowguid by

  Set <@someVariable> =(select i.rowguid from deleted)

  and other columns then you insert into other table using insert and <@somevaraible>

  Arshad