Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Send Email every 30 minutes if Insert Happens??? Expand / Collapse
Author
Message
Posted Sunday, July 06, 2008 2:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Sunday, July 06, 2008 11:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 31, 2014 7:18 PM
Points: 814, Visits: 2,400
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
Posted Monday, July 07, 2008 4:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 31, 2014 7:18 PM
Points: 814, Visits: 2,400
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, July 09, 2008 9:41 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 29, 2013 7:54 AM
Points: 438, Visits: 920
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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 29, 2013 7:54 AM
Points: 438, Visits: 920
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 12»»

Permissions Expand / Collapse