How to send email last updated(Inserted) records from AuditLog table using Stored Procedure in SQL SERVER

MSBI_Developer_1990 41 Reputation points
2022-05-24T11:14:35.44+00:00

Hi Folks,

i have AuditLog table in my DB. This table is captureing mutiple ETL run information. i want to send email current records on every run. Below is my sample table. Can you please help me how to write store procedure to achive this. if any sample code would be more helpful for me. my client said No Triggers.

205121-audit-log.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,743 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-25T07:25:08.753+00:00

    Hi @MSBI_Developer_1990
    First, you need to configure database mail in SQL Server. Refer to this article: Configure Database Mail

    i want to send email current records on every run.

    It seams using a DML trigger with a reminder e-mail message will be a good solution. Refer to this thread for more details: Send e-mail from a trigger

    However, considering that your clients don't want to use triggers, you can try setup a SQL job, with a stored procedure to check the table periodically and send out an email as needed.
    Use the SQL Server Change Data Capture or Change Tracking features(CHANGETABLE function) to get the changed records for a table and information about these changes. It queries the records stored in the internal change tracking tables.
    Then, a stored procedure that reads the records should be used, and a job that will run once in a while.

    A drawback of these features is that they do not track the time of the change nor old and new values of the changed record. Since the operation is synchronous, it prolongs the transaction time. Another drawback is that you have to query the tables periodically and then send the notification email.

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments