If statement/Status/getdate() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

If statement/Status/getdate()

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-25 : 12:41:35

Hi I am new to SQL and need help to figure out something...

Basically what I am trying to do is creating a trigger so that whenever there is a new entry added in the table it copies the row and paste it paste it in the new table(new_table and from that table it will send an email to person abc@domain.com

I got everything figured out including it's sending an email to abc@domain.com but the problem whenever there is a new customer added it sends an email with the list of all the new customers added previously from the(new_table)
What I would like to do is send and email whenever there is a new customer added that particular day. For example if there is a new customer added (Customer A) on Monday April 5 at 10:00 am.I want to send an email to abc@domain.com that day only with Customer A AND if later in the day there is a new customer added(Customer B) on the same day around 11:00 am I would like to send an email again with Customer A and Customer B's information but if there is a new customer added on April 6th then I only want to send an email to xyz@domain.com with the list of customers added on April 6th and not April 5th

I know you can probably do that using getdate() function or something but I am not sure how..and I beleive it has to be somewhere by the "IF" statement??
OR I can use status field but I need help with implementing it but not sure how?

ALTER TRIGGER Trigger1
ON m_user
FOR INSERT
AS
INSERT Trigger_table(company_name, locn, user_id)
SELECT company_name, locn, user_id
FROM inserted

if (select count(*) from sms..Trigger_Project where company_name='ABC') >0

BEGIN
EXEC master..xp_sendmail
@recipients = 'xyz@domain.com',
@query = 'select * from dbs..Trigger_Table where company_name=''XYZ''',
@subject = 'xyz',
@message = 'New customer added today',
@width = 350

END

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-25 : 12:56:19

If you want to use time

Add a date/time field with a default of Getdate()

Example name : Insfdate

Then

ALTER TRIGGER Trigger1
ON m_user
FOR INSERT
AS
INSERT Trigger_table(company_name, locn, user_id)
SELECT company_name, locn, user_id
FROM inserted
Where Insfdate between dateadd(mi,-2,Getdate()) and Getdate()
--- From last two minutes
if (select count(*) from sms..Trigger_Project where company_name='ABC') >0

BEGIN
EXEC master..xp_sendmail
@recipients = 'xyz@domain.com',
@query = 'select * from dbs..Trigger_Table where company_name=''XYZ''',
@subject = 'xyz',
@message = 'New customer added today',
@width = 350

END

Jim
Users <> Logic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 13:19:53

As Jim said, You can use a datetime column to compare to getdate():
and datediff(hour, [DatetimeCol], getdate()) <= 24 --within last 24 hours

But I don't think you want the comparison on the insert, you want it in the IF and the @query, right?

Couple of suggestions...
Use exists instead of the result of count(*) because it's more efficient.

For the @query parameter, select an explicit column list.
In fact, always use an explicit column list. The exceptions are when
using EXISTS/NOT EXISTS.


--define query to select all records for ABC added within last 24 hours
declare @query varchar(2000)
set @query = '
select	<colList> 
from	dbs..Trigger_Table 
where	company_name=''XYZ'' 
and	datediff(hour, [DatetimeCol], getdate()) <= 24)
'

--if any records have been added for ABC within last 24 hours
if exists (select * from sms..Trigger_Project 
	where	company_name='ABC'
	and	datediff(hour, [DatetimeCol], getdate()) <= 24)
begin
	EXEC master..xp_sendmail 
	@recipients = 'xyz@domain.com', 
	@query = @query, 
	@subject = 'xyz',
	@message = 'New customer added today',
	@width = 350
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources