application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>
|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>
|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>
|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.