Re: Email trigger not working - please help
From: Mike John (Mike.John_at_knowledgepool.spamtrap.com)
Date: 08/13/04
- Next message: zeyneddine: "Re: Sum of a column obtained from select query"
- Previous message: Calvin X: "UDF and SQL2000 - Why doesn't this work?"
- In reply to: Victor: "Email trigger not working - please help"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Aug 2004 20:40:43 +0100
Victor,
very difficult to tell wht is not working without a better description of 'not working', but some things to chec;
It will only ever handle single row inserts - if someone ever dores an insert into - select from thren it will break as multiple rows will be in the inserted table. this may (or my not) be your problem.
tblhealth - if this is really a table name I would consider renaming - search this group for loads of discussions on such things as tbl prefixes!
No need to call xp_startmail - mail system gets started on first use of xp-sendmail anyway.
Is the cal to xp_sendmail failing - you have no error checkingso it may be - easiste way to trace the whole thing is creata a simple stored proc that just inserts into your table, then use the debugger in QA to execute this stored proc - you can then single-step through the trigger and seewhat is going on.
May also be preferable to simply insert a row into a logging table when the insert happens, and then have a scheduled job that polls that table for new rows and emails from there - avoids stopping your main app if you get a mail problem in sql server.
Mike John
"Victor" <vtipi@msn.com> wrote in message news:77988a7f.0408131128.5e56e686@posting.google.com...
> Hello,
> Can someone look at this trigger and let me know what I am doing
> wrong.
> The trigger is supposed to send out an email when a new record is
> inserted into the table(tblHealth)
>
> CREATE TRIGGER health
> ON tblHealth
> FOR INSERT
> AS
> DECLARE @hName nvarchar,@DateOfBirth smalldatetime,@Height
> nvarchar,@Weight float, @TobaccoUse nvarchar,@SpouseName
> nvarchar,@SpouseDateOfBirth smalldatetime,@SpouseHeight nvarchar,
> @SpouseWeight float,@SpouseTobaccoUse nvarchar,@TypeOfCoverageDesired
> nvarchar,@Remarks nvarchar, @Address nvarchar,@Phone
> nvarchar,@BestTimeToContact nvarchar,@PersonToSpeakWith
> nvarchar,@EmailAddress nvarchar
>
> select @hName = hName,
> @DateOfBirth = DateOfBirth,
> @Height = Height,
> @Weight = Weight,
> @TobaccoUse = TobaccoUse,
> @SpouseName = SpouseName,
> @SpouseDateOfBirth = SpouseDateOfBirth,
> @SpouseHeight = SpouseHeight,
> @SpouseWeight = SpouseWeight,
> @SpouseTobaccoUse = SpouseTobaccoUse,
> @TypeOfCoverageDesired = TypeOfCoverageDesired,
> @Remarks = Remarks,
> @Address = Address,
> @Phone = Phone,
> @BestTimeToContact = BestTimeToContact,
> @PersonToSpeakWith = PersonToSpeakWith,
> @EmailAddress = EmailAddress
> from inserted
>
> --send out the E-mail to let people know that a quote is requested!
> declare @Message nvarchar(1024)
> declare @Email nvarchar(128)
> declare @Subject nvarchar(128)
> Select @Message = 'New quote requested by: ' + i.hName +'
> Date Initiated: ' + convert(varchar(32),GetDate()) + '
> Name: ' + hName + '
> Date Of Birth: ' + DateOfBirth + '
> Height: ' + Height + '
> Weight: ' + Weight + '
> Tobacco Use: ' + TobaccoUse + '
> Spouse Name: ' + SpouseName + '
> Spouse Date Of Birth: ' + SpouseDateOfBirth + '
> Spouse Height: ' + SpouseHeight + '
> Spouse Weight: ' + SpouseWeight + '
> Spouse Tobacco Use: ' + SpouseTobaccoUse + '
> Type Of Coverage Desired: ' + TypeOfCoverageDesired + '
> Remarks: ' + Remarks + '
> Address: ' + Address + '
> Phone: ' + Phone + '
> Best Time To Contact: ' + BestTimeToContact + '
> Person To Speak With: ' + PersonToSpeakWith + '
> Email Address: ' + EmailAddress
> from inserted i
>
> --Get the E-mail address of the persons who needs updating...
> set @Email = 'vtipi@msn.com'
>
> --Get the Subject line
> set @Subject = 'New Quote Requested '
> exec master..xp_startmail
> exec master..xp_sendmail @recipients = @Email,
> @message = @Message,
> @subject = @Subject
- Next message: zeyneddine: "Re: Sum of a column obtained from select query"
- Previous message: Calvin X: "UDF and SQL2000 - Why doesn't this work?"
- In reply to: Victor: "Email trigger not working - please help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
