Hi All,
I'm trying to create a trigger to fire an email after a bit field has the
value 1.
MyTable has 4 field the one I'm tracking is fcheck. With the sample above
I'm able to do the email when the row is updated.
What about when is a new row? Can I do it here or I need to create another
trigger?
Tks
Johnny

CREATE TRIGGER [Email Alert] ON [dbo].[myTable]
FOR INSERT, UPDATE
AS

DECLARE @oldstatus int
DECLARE @newstatus int
DECLARE @content varchar(4000)
DECLARE @subjectDesc varchar(4000)
DECLARE @rc int

SELECT @oldstatus = fcheck FROM deleted
SELECT @newstatus = fcheck FROM inserted


IF (@oldstatus = 0 AND @newstatus = 1)
BEGIN
--- Send email Alert
END

"JFB" wrote ...

I think you've already got it here JFB.

Note the "FOR INSERT, UPDATE" - these are the conditions which will cause
the TRIGGER to run, ie, when a row is INSERTED, or UPDATED.

I dont personally favour Triggers, my main reason is that they are easily
forgotten about, ie, 2 years into your application running you want to
change your emails and say to yourself "what the hell is sending them, I
cant find the code anywhere" - of course, hidden nicely on your table is
this trigger sneaking around :o)

If it were me, and this might not be possible for yourself, I would probably
use a Stored Procedure to update the bit field to 1, and then run some code
to send an email at the same time.  It's a bit easier to spot a SP,
especially if its named appropriately.

Hope this helps.

Regards

Rob

Try this and let me know if this helps
Change this
IF (@oldstatus = 0 AND @newstatus = 1)
BEGIN
--- Send email Alert
END

to
IF ((@oldstatus = 0 or @oldstatus is null) AND @newstatus = 1)
BEGIN
--- Send email Alert
END

hope this helps
Show quoteHide quote

Don't send emails from triggers - it slows down inserts and updates for no
reason.

What if sending the email fails? Is the insert/update still valid?
What if the transaction is rolled back? Will you issue an email-recall?

Use the trigger to propagate changes to another table, then use a SQL Agent
Job to scan this table every now and then, send the notofications, then
delete the rows from this table.


ML

---
http://milambda.blogspot.com/

On Fri, 28 Apr 2006 09:22:47 -0400, JFB wrote:

Hi Johnny,

Don't. A trigger runs within the context of a transaction. One of the
issues you have to keep in mind is that the changes can still be rolled
back. What if an error causes the transaction to be rolled back after
the mail is sent?

Also, sending mail is a relatively slow process. A trigger can't do this
asynchronously. So the performance of your app will decrease and
scalability is out of the window if you send mail from your triggers.

A much better way is to add rows to a table with information about mail
to be sent. Then set up a SQL Agent job that periodically polls this
table and sends the mails.

(If you're on SQL Server 2005, things are different. You can use
Database Mail to send your mails. DB Mail is functionallly the same
process as I suggest, except that Service Broker is used to provide
asynchronous communication between the component that generates the mail
request and the component that sends it, and that all functionality for
reading the queue and sending the mail is inclluded in SQL Server.)

(snip)
Don't do this either. A trigger fires once per statement execution, not
once per row affected. That means that you should always code your
triggers to ensure that they work correctly for single-row, multi-row
and zero-row operations.

If your trigger is fired by an UPDATE statement that affects several
rows, @oldstatus and @newstatus will end up being equal to the status of
"one of the rows" in inserted and deleted.

Your trigger needs to check and see if more than one row was inserted or
updated.

THe first statements should be

declare @myrowcount

set  @myrowcount = @@rowcount

if  @myrowcount = 0 return
if @@rowcount > 1 raiserror "I don't know how to handle more than one row at
a time" return

Then you can check to see if there are any rows in the deleted table. The
deleted table will never contain rows for an insert.
It will always contain at least one row if a row was updated.

Insert multiple lines
Strange behavior with MS SQL
Please help on this matching problem
DISTINCT returns duplicates
Local MDF Files--can I make this work?