Send Email every 30 minutes if Insert Happens???

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 7,2000 » SQL Server Agent » Send Email every 30 minutes if Insert...

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

Send Email every 30 minutes if Insert Happens???

Expand / Collapse

Author

Message

Posted Sunday, July 06, 2008 2:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309

Dear All,
I need to do the following:
I need to send an Email every 30 minutes is Insert happenned on 'myTable'...
I just do not know where to go to implement that.

I think this can be done by creating Job and schedule it, if this is right, I've created a job, but I could not create a step to check if an Insert happenned then fire an alert to send an Email.
Plz help to accomplish this issue... I think this is just like the one in this web site (when you create a job alert - when you go to SQL Jobs).

Summery: How to send an Email every 30 minutes or every day if Insert operation has been done on "myTable"???

OBarahmeh

Post #528932

Jeffrey Williams 3188

Posted Sunday, July 06, 2008 11:23 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

Well, the first thing you need to do is identify how you will determine whether or not your table has a new row (inserted).

Do you have any date columns on this table? Is one of them a created date for the row? Can you verify that the information in that column is only modified when a new row has been created?

If so, then you could do something like:

If Exists (Select * From MyTable Where datecolumn >= dateadd(minute, -30, getdate()))
     Begin;
          -- code to send mail here
     End;

If you don't have any date columns to identify this - then you might be able to do something with an identity column if you have one on your table. This would involve something like:

Declare @seed int;
Set @seed = (Select seed From LastSeedTable);

If Exists (Select * From MyTable Where identityColumn > @seed)
     Begin;
          Update LastSeedTable
                    Set seed = (Select max(identityColumn) From MyTable);

          -- code to send email

     End;

You can then put the above in a stored procedure and call it from a SQL Agent job - or, just put the code in the Agent job.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #528976

Posted Monday, July 07, 2008 4:17 PM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:25 PM
Points: 778, Visits: 2,314

how about creating a trigger on 'myTable' for inserts, and creating a separate 'audit' table

When a new row is inserted into 'myTable', insert a row into the audit table with the date/time of the insert and any other values needed -

then set up the scheduled job to run every 30mins and check the dates in the audit table, sending an email if one or more of the rows was inserted in the previous 30mins

Post #529681

Jeffrey Williams 3188

Posted Monday, July 07, 2008 4:30 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

Ivanna Noh (7/7/2008)

how about creating a trigger on 'myTable' for inserts, and creating a separate 'audit' table

When a new row is inserted into 'myTable', insert a row into the audit table with the date/time of the insert and any other values needed -

then set up the scheduled job to run every 30mins and check the dates in the audit table, sending an email if one or more of the rows was inserted in the previous 30mins

If there are no date columns or identity (or other monotonically increasing value) on the table, then yes - this would be another option.

I wouldn't implement a trigger just for this purpose though, especially if all it takes is a simple query to identify the new rows.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #529687

Posted Monday, July 07, 2008 4:37 PM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:25 PM
Points: 778, Visits: 2,314

I wouldn't implement a trigger just for this purpose though, especially if all it takes is a simple query to identify the new rows.

Agreed, triggers are a bit of a last resort! However, the OP hasn't made it clear if there is a column in myTable that could be used to identify when the insert occurred :)

Post #529691

Posted Wednesday, July 09, 2008 3:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309

Thanks for ur Comments and reply,
First: I have two columns; one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.

And I get these two dates when the rows are inserted into the table. So I used the first option to get the last rows in the last 30 minutes, which is by:

What I did is: I've created a job with one step containing the following code to check the new rows and then send email, and schedule this job to run every 30 minutes.

If Exists (Select * From MyTable Where DayTransTime >= dateadd(minute, -30, getdate()))
Begin;
code to send email
End;

I tried the previous code and email is sent successfully, BUT this code is always sending Email every 30 minutes and does not behave that it checks if new rows had been inserted in the last 30 minutes.
I don't know if you gues got the problem or not, but I tried it and email is sent every 30 minutes wheather there are new rows or not.

Note # 1: I don't wanna send Email if there is no new rows inserted in the last 30 minutes.

Note # 2 (important): I passed the body of the Email manually (normal text), but I need to send the new rows inserted in the last 30 minutes...Is this possible???

Post #530585

Jeffrey Williams 3188

Posted Wednesday, July 09, 2008 9:41 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

obarahmeh (7/9/2008)

Thanks for ur Comments and reply,
First: I have two columns; one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.

Dates should be stored in the database as datetime data type.

You are going to have to convert the decimal date and time into a valid datetime column for the comparison. I think this might do it:

SELECT columns
FROM table
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6), 5, 0, ':'), 3, 0, ':'))
> DATEADD(minute, -30, getdate());

As for sending the rows from the query, I don't know what you are using to send the mail. In SQL Server 2005 - you could use sp_send_dbmail to send the above query.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #530980

Posted Thursday, July 10, 2008 12:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309

Dear Jeffrey,

My application needs to store the dates as Decimal for some requirement, and your opinion to convert the decimal into date is right, and I was trying to write the query for this conversion.
Any way, I tried your query, but there is an error that I could not figure it out...this is because I did not use the STUFF function before and have no idea how it works.
please try to use your query in your SQL server 2005 and check if every thing works well or not.

This is your query with one ")" I think you missed:

SELECT * FROM MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6), 5, 0, ':'), 3, 0, ':')))
> DATEADD(minute, -30, getdate());

With my best Regards to you...

Post #531382

Posted Thursday, July 10, 2008 7:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 06, 2013 6:31 AM
Points: 438, Visits: 919

To actually send an email notification via SMTP, WITHOUT requiring that Outlook be running on the host machine of SS2K and WITHOUT having to configure an email account in SS2K, the code has already been posted at this URL:

http://www.sqlservercentral.com/Forums/Topic527864-110-1.aspx

Have fun

Post #531598

Posted Thursday, July 10, 2008 7:51 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 06, 2013 6:31 AM
Points: 438, Visits: 919

Regarding the use of Triggers:

The one reason I avoid triggers like the plague is because these are unseen, i.e. if a stored procedure (for instance) is found to be causing trouble down the road, it is too easy to forget checking - or too cumbersome -- whether or not each of the tables used by the stored procedure has a trigger.

Any further input on why triggers should be avoided ?

Regards

Post #531620

« Prev Topic | Next Topic »

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse