Database alert if no data added

Question

Database alert if no data added

tsiryelhy

SSC-Addicted

Points: 417
More actions
May 3, 2021 at 12:28 pm

#3878720
Hi friends,
I’d like to have your help. I have a SQL Server database, and a table dbo.OnData on it.
I would like to receive an email alert from my database if:
- within a range of 30mins from 8:00:00 a.m. opening time and default time per day
- AND 30mins after the time of the last Add in the dbo.OnData table,
there are no add to the dbo.OnData table, a message like "from such time to such time, No Add to dbo.OnData table".
Example:
Opening at 08:00:00,
From 08:00:00 to 08:30:00, there is no add in the table, => Alert
From 08:30:00 to 9:00:00, 1 line has been added at 08:35:00 => No alert
From 08:35:00 (time when the last rows were added) to 08:35:00 + 00:30:00 i.e. 09:05:00, there is no add in the table, => Alert
From 09:05:00 to 09:35:00, there are 20 lines added, last line time at 09:15:00 => No alert
From 09:15:00 to 09:45:00, there is no add in the table => Alert
Would it be possible to do this?
Thanks in advance friends,
- This topic was modified 2 years, 11 months ago by tsiryelhy.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1333 More actions · Answer 1

Assuming you have a CreatedDateTime or equivalent that is always populated (e.g., has a default value & nobody explicitly sets it to null), yes, it seems possible.

You'd probably use a SQL Server Agent job that runs on your specified schedule, queries to see if there are any CreatedDateTime values between PeriodStartTime and PeriodEndTime, and then execute sp_send_dbmail (assumes you've enabled & configured database mail)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

How can you tell if there is more data in the table? You can count rows, but if someone adds a row and deletes a row, you won't be alerted.

As noted, once you have a way to determine if data has been added, and you can write a query that returns some value to tell you if data has been added, you can schedule this query as an alert.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Make it easy on yourself... add a trigger to the table to count "what's happening" to the table and capture it in a "what happened at what time" history table. Then, setup a job to run every N number of minutes to interrogate that table. This would be a great place to have a clustered index in descending order based on the temporal column that contains "when it happened".

Done correctly, you won't even notice the trigger is active.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

I would create a job that runs every 5 minutes with code in it something like this:

DECLARE @CurrentDateTime as datetime = GETDATE()
IF NOT EXISTS(SELECT * 
                FROM dbo.OnData d
               WHERE d.DateStamp > DATEADD(mi,-30, @CurrentDateTime ))
BEGIN

    -- Code here to raise alert
    SELECT 'Alert no rows from ' + LEFT(CONVERT(varchar, DATEADD(mi,-30, @CurrentDateTime ), 121),20) + ' to ' + LEFT(CONVERT(varchar, @CurrentDateTime, 121),20)

END

The job should start at 8:30 am and finish at closing time.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Let's hope they have such a temporal column available on the table and that it defaults to the current date and time on insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

tsiryelhy wrote:

Hi friends,
I’d like to have your help. I have a SQL Server database, and a table dbo.OnData on it. I would like to receive an email alert from my database if: - within a range of 30mins from 8:00:00 a.m. opening time and default time per day - AND 30mins after the time of the last Add in the dbo.OnData table, there are no add to the dbo.OnData table, a message like "from such time to such time, No Add to dbo.OnData table".
Example: Opening at 08:00:00, From 08:00:00 to 08:30:00, there is no add in the table, => Alert
From 08:30:00 to 9:00:00, 1 line has been added at 08:35:00 => No alert
From 08:35:00 (time when the last rows were added) to 08:35:00 + 00:30:00 i.e. 09:05:00, there is no add in the table, => Alert
From 09:05:00 to 09:35:00, there are 20 lines added, last line time at 09:15:00 => No alert
From 09:15:00 to 09:45:00, there is no add in the table => Alert
Would it be possible to do this?
Thanks in advance friends,

"Enquiring minds want to know"... do you actually have a temporal column on the table that defaults to the current date and time or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tsiryelhy SSC-Addicted Points: 417 More actions · Answer 7

@jeff Moden

No I don't have this column in my table, just a datetime column for the date and time of an added row

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

tsiryelhy wrote:

@jeff Moden
No I don't have this column in my table, just a datetime column for the date and time of an added row

Ok... that's kind of the same thing. Just stand up a job that checks what the max value in that column is once every x number of minutes, as the others have suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)