is there a way i can get an alert whenever a table gets records in sql server

Question

i have a table in SQl server which occasionally gets data from a linked server, and than i have to do activities on it .

but the problem is there is no way to check if the data is inserted in table (table is always truncated after performing the activity so next time when data is pushed table is already empty) i manually check daily for data if it is inserted or not .

what i want is to get auto alert on my email (i already have db_mail configured and working) whenever the data is pushed in a table .

i have sa admin and complete privileges on Database and also on Windows server 2012 R2

how to do that can you please explain it. i know about sending email from db but not about triggers, thanks — Nasir Iqbal, Feb 15 '19 at 07:15
Thats a topic far too large for a question on SO. I suggest using google to find a relevant tutorial. — Dale K, Feb 15 '19 at 07:37
@NasirIqbal You can use server-sent events for that purpose. https://stackoverflow.com/questions/14564903/server-sent-events-and-php-what-triggers-events-on-the-server — Sayed Mohd Ali, Feb 15 '19 at 10:36

EzLo · Accepted Answer · 2019-02-15T10:47:32.623

You can do this with a trigger but you will have to do some preparations with privileges so the executor (the login that's inserting the records on your tracking table) can send email correctly:

CREATE TRIGGER dbo.TrackingTableNameAfterInsert ON TrackingTable
    AFTER INSERT
AS
BEGIN

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourConfiguredProfile',
        @recipients = 'youremail@mail.com',
        @subject = 'Records were inserted on TrackingTable',
        @body = ''

END

You might want to encapsulate the email sending on an SP and configure it's permissions there.

In regards to the following:

...table is always truncated after performing the activity so next time when data is pushed table is already empty...

You can create a historical table and use a trigger to also insert inserted records on this table, so the TRUNCATE or DROP of the original one won't affect the copied records.

CREATE TABLE TrackingTableMirror (
    /*Same columns and types*/
    InsertedDate DATETIME DEFAULT GETDATE())
GO

CREATE TRIGGER dbo.TrackingTableInsertMirror ON TrackingTable
    AFTER INSERT
AS
BEGIN

    INSERT INTO TrackingTableMirror (
        /*Column list*/)
    SELECT
        /*Column list*/
    FROM
        inserted AS I

END

This way you can check all records on this mirrored table and not the volatile one (and avoid all the email sending).

thank you @EzLo that solutions (trigger part) worked and it was very simple. for the second part where you suggested about mirror table, i am already doing that to maintain history. Cheers! — Nasir Iqbal, Feb 18 '19 at 07:25

score -1 · Answer 2 · edited Feb 21 '19 at 23:10

1) Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings.

2) Run Query

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

3)

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

through the table

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

    EXEC @query
    SELECT @id=MIN(id) FROM [email_adresses] where id>@id

END

4) Trigger Code

CREATE TRIGGER [dbo].[Customer_INSERT_Notification]
       ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @CustomerId INT

       SELECT @CustomerId = INSERTED.CustomerId      
       FROM INSERTED
       declare @body varchar(500) = 'Customer with ID: ' + CAST(@CustomerId AS VARCHAR(5)) + ' inserted.'
       EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Email_Profile'
           ,@recipients = 'recipient@gmail.com'
           ,@subject = 'New Customer Record'
           ,@body = @body
           ,@importance ='HIGH'
END

I refer this link.

thanks for the detailed answer @addweb but i already solved it and about first 3 parts it was already configured just needed 4th part where i needed a trigger after insert. — Nasir Iqbal, Feb 18 '19 at 07:29

is there a way i can get an alert whenever a table gets records in sql server

2 Answers2