Track data change in a table.

• lcarrethers

  SSCommitted

  Points: 1917

  More actions

  October 29, 2009 at 9:12 am

  #1072524

  There is no error. It acts as if it runs but i never get a email, but it does update the table.

  i also created a stored procedure and tried to have the trigger call that, but i never get a email.

  If I run exec AUDIT_UPDATE_RM00101email which is the name of the sp from management studio is sends the email fine

  Here is the sp i created.

  -- =============================================

  -- Author:<Larry Carrethers>

  -- Create date: <11/29/2009>

  -- Description:<This Stored Procedure sends a email out after a address has been

  -- Changed in GP. This is called by AUDIT_UPDATE_RM00101 Trigger>

  -- =============================================

  ALTER PROCEDURE [dbo].[AUDIT_UPDATE_RM00101email]

  -- Add the parameters for the stored procedure here

  AS DECLARE

  @user-id varchar(20),

  @getdate-2 varchar(39),

  @OldValue varchar(50),

  @NewValue varchar(50),

  @FieldName varchar(50),

  @SQL varchar(1000),

  @DBNAMEvarchar(10)

  SET NOCOUNT ON;

  SET@DBNAME= 'Test5' --set this variable as the name of your database

  SET@SQL= 'SELECT Date_Changed, FieldName, OldValue, NewValue, UserID

  FROM ' + @DBNAME + '.dbo.AUDIT_Table WHERE Date_Changed >= ' + char(39) +

  convert(varchar(21), GetDate(), 101) + char(39)

  EXEC master.dbo.xp_sendmail

  @recipients = 'lcarrethers@dolese.com',

  -- @copy_recipients = @COPY_RECIPIENTS,

  -- @blind_copy_recipients = @BLIND_COPY_RECIPIENTS,

  @subject = 'Address changed',

  @message = "Attached is a list of address's that have been changed today",

  @query = @SQL,

  @attach_results = 'TRUE',

  @width = 250