Trigger/Counters/?? - 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)

Trigger/Counters/??

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-24 : 22:24: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??

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

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-24 : 22:41:14

how are you sending the email?
1. we do it via trigger for every inserts
2. add a field status to your newtable with default of false or 0 or whatever you think it will be easier for you (datatype int or varchar or char), we use bit with a default of 0 for not sent and 1 for sent,

for every successful email, update the field to 1

then in your query, query the records with value 0 for this field, this will also cover those rows that have not been sent previously

HTH

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-24 : 22:50:41

This is how I am sending an email..I know I can probably do it by using by adding a status field..how?? but I would like to do it by using the getdate() and convert date..can you help me how to use those functions?

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_table 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

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-24 : 22:50:44

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-24 : 23:16:37

the trigger for sending email must be on the newtable not on m_user,
any inserts in the newtable will send the email to whomever your recipient is

for adding another field, check ALTER TABLE in BOL or use Enterprise Manager, remember to define a default value so that every inserted row will have status 0 (not sent)

in the trigger, modify the query with a "where status=0" or if you're really sure that every request for sending email will be successful,

query should be: 'select * from inserted where company_name=''XYZ'''

if sendmail is successful then update the status to 1 for those rows

HTH

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-24 : 23:21:32

Well, The trigger is right..because i am using only one trigger to perform the whole operation. For example when there is a neww row added in the main table it uses the trigger in that table and copy it into a new table.
From new table it sends an email to xyz@domain.com and i have tested it..it's been working great. The only concern I have is the not sending the email twice and I want to use getdate() function but not sure how to use it??

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-24 : 23:32:25

what i'm advising you is not to use the getdate() function, keep it simple, instead of trying to determine if the email has been sent for set number of seconds or even minutes (let's say you've concluded that the mail can be sent for n-number of time units), how sure are you that the email has been sent aside from asking the customer?

but it's your choice, if you have a datetime field, check
datediff in BOL

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-24 : 23:49:20

Thanks for your advice. I will use ststus field but like I said I am new..can you show me how to use status and 0 and 1 for it?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-25 : 00:03:22

alter table newtable
add status bit default 0
go

for your current rows:

update newtable
set status=1 ' this is to set these previous rows to sent

modify your trigger, check previous post on what to look for

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-25 : 00:30:45

Well, I am asking you for the syntax if you could provide? I have added a new field Status and set the default to 0. When I run the query it gives me a Null value. Now I am not sure how to change it to 1 after the email has been sent. I have provided the code above and need help in code language so if you could help that would be great?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-25 : 00:37:41

check the syntax in BOL, it's always great to learn something new

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-25 : 00:45:25

Thanks for wasting my time. If you dont' know something please do not pretend that you know and wasted your and other people's time.
It's always easy to advice someone what to do than to actually do. I already knew what needed to be done all I was asking how to do it..and you wasted my time..becuase you probably dont know yourself what is supposed to be done

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-25 : 01:01:36

excuse me for trying to teach you to fish instead of giving you the fish

--------------------
keeping it simple...

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-25 : 12:36:40

Yeah but the difference is that the teacher itself doesn't know how to fish

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-25 : 12:50:26

>>.Yeah but the difference is that the teacher itself doesn't know how to fish

Wrong. You really need to learn the basic operations of SQL -- INSERT, SELECT, UPDATE, etc -- before messing around with triggers and sending emails and things of that nature, which are obviously more complex. It's not our job to tutor you in SQL; it is your responsibility to take some initiative and learn the basics and to make an effort to find the answer yourself before you ask others to provide the answer for you.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources