SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send Email every 30 minutes if Insert Happens???


Send Email every 30 minutes if Insert Happens???

Author
Message
obarahmeh
obarahmeh
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20069 Visits: 10042
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Ivanna Noh
Ivanna Noh
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 3047
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20069 Visits: 10042
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Ivanna Noh
Ivanna Noh
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 3047
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 Smile
obarahmeh
obarahmeh
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 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???
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20069 Visits: 10042
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 opportunities brilliantly disguised as insurmountable obstacles.

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

obarahmeh
obarahmeh
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 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...
J-440512
J-440512
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 949
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
J-440512
J-440512
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 949
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search