Trigger

• yvr4

  Default port

  Points: 1400

  More actions

  June 21, 2007 at 4:20 pm

  #117533

  I am having a table called sonic. whenever any

  changes insert/delete/update done in the table i

  need to send a email alert . please some body give me sample code.

• michaela

  SSCertifiable

  Points: 5649

  More actions

  June 21, 2007 at 7:07 pm

  #714480

  Assuming you've already installed dbmail on your instance then the code should look like:

  SET

  ANSI_NULLS ON

  GO

  SET

  QUOTED_IDENTIFIER ON

  GO

  CREATE

  TRIGGER schema_name.trigger_name

  ON schema_name.table_name

  AFTER

  <INSERT,DELETE,UPDATE>

  AS

  BEGIN

  declare @var bigint

  set @var=0

  select @var=count(*) from inserted,deleted

  if @var>0 then

  EXEC msdb.dbo.sp_send_dbmail @recipients='you@yourdomain.com',

  @subject

  = 'Sample: Data values modified',

  @body

  = @bodymsg,

  @body_format

  = 'HTML'

  END

  GO

  I would not do this if I'd have bulk inserts/updates/deletes.

  Good luck.