Trigger on update

Question

Post reply

Trigger on update

feuerrca

Grasshopper

Points: 17
More actions
August 27, 2003 at 12:07 am

#160627

Hi, I am trying to write a trigger that fires off an email when a table is updated.
he code I am using is:
Create Trigger neworder1 ON statorder AFTER UPDATE AS
EXEC master..xp_startmail
EXEC xp_sendmail
@recipients = 'carol.feuerriegel@mcmahonclarke.com',
@query = 'SELECT * FROM statorder where orderdate LIKE getdate()',
@subject = 'Today's Orders',
@message = 'The orders for today:',
@attach_results = 'TRUE', @width = 250
EXEC master..xp_stopmail

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

Hi Carol,

What's your question?

Also, do you really want an email for every update on the table?

How about an end of day scheduled task instead?

BTW: I dont think "orderdate LIKE getdate()" is going to work.

Cheers,

- Mark

Cheers,
- Mark

feuerrca Grasshopper Points: 17 More actions · Answer 2

Hi Mark, Yes! I do want an update ( an end of day report would be great too but I am starting with small steps!) and No! LIKE getdate () doesn't work.

My question is what syntax do you use for the email alert on an Update action?

Any assistance is greatly appreciated.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Carol,

I think that the below might work for you:

Create Trigger neworder1 ON statorder AFTER UPDATE AS

EXEC master..xp_startmail

EXEC xp_sendmail

@recipients = 'carol.feuerriegel@mcmahonclarke.com',

@query = 'SELECT * FROM statorder where CONVERT(VARCHAR, orderdate, 101) LIKE CONVERT(VARCHAR, getdate(), 101)',

@subject = 'Today''s Orders',

@message = 'The orders for today:',

@attach_results = 'TRUE', @width = 250

EXEC master..xp_stopmail

I modified the date criteria to for both the order date and getdate to use just the date and not the time.

Also, I modified your subject to add another quote in Today's Orders to be Today''s Orders. This allowed the code to run without an unclosed quote problem.

I hope this helped.

AJ Ahrens

SQL DBA

Revenue Assurance Management - AT&T

Good Hunting!

AJ Ahrens

webmaster@kritter.net

feuerrca Grasshopper Points: 17 More actions · Answer 4

Thankyou AJ, Unfortunately it still falls over 'near AFTER'.

I'm still working on it!

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Carol,

Please find a modified version of the TRIGGER. I had tested it with different tables and apologize for the problem.

The issue is (I think) that you need to qualify the database and/or owner of the table you are doing your select against. Also, (at least on my system) you need to qualify the xp_sendmail to be coming from master...

I hope this new TRIGGER helps [:-)]

I do have a question though. This will ONLY fire after an UPDATE and not on INSERT. Is this the intent??

CREATE TRIGGER neworder1 ON statorder AFTER UPDATE AS