Getting Column values of Updated Row using Trigger

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

SQL Server Development (2000)

Getting Column values of Updated Row using Trigger

Author

Topic

vijaykvr
Starting Member

5 Posts

Posted - 2003-04-19 : 10:02:22

I wrote the following trigger to update a row which in turn sends a mail using xp_sendmail.
I need to get other columns values of the updated row and I did it using the
first select statement found in the trigger. I find that irrespective of any row
that is being modified, the record pointer moves to the bottom most row if more
than one rows exist. How do I correct this bug? Is there any option other than creating
a temporary table as it would be a tedious procedure. Also, I find SQL7 doesnot contain
mailaddress as a distinct datatype!!!! Please find below the code I have used.
I would greatly appreciate a reply from you.

CREATE TRIGGER statusreminder
ON [tinfo]
FOR UPDATE
AS
declare
@mailid varchar(30),
@wlno nvarchar(10),
@pname nvarchar(120),
@ctime nvarchar(20),
@cmessage varchar(500)
select @mailid=maddress,@wlno=wno,@pname=pname,@ctime=getdate() from [tinfo]
set @cmessage = 'Hello ' + @pname +'This is in response to your registration with us '+ @wlno + 'This mail was sent at ' + @ctime +'Thank you for registering with us.'
EXEC master..xp_sendmail @recipients = @mailid,@message=@cmessage,@subject = 'Status Update...'

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-19 : 10:32:24

quote:
select @mailid=maddress,@wlno=wno,@pname=pname,@ctime=getdate() from [tinfo]

No vijay, this statement will not give you the column values of the inserted row...you need to use the INSERTED table provided by SQL Server, which contains the newly updated row.

Hence your statement will be:
select @mailid = i.maddress, @wlno = i.wno, @pname = i.pname, @ctime = getdate() from INSERTED i

quote:
Also, I find SQL7 doesnot contain
mailaddress as a distinct datatype!!!!

huh?

OS

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-19 : 14:14:19

See
www.nigelrivett.com
Triggers 1

mailaddress?
What would you expect that to be? And why would you expect a relational dtabase to tie itself to a specfic mail format?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vijaykvr
Starting Member

5 Posts

Posted - 2003-04-23 : 01:10:41

quote:

I wrote the following trigger to update a row which in turn sends a mail using xp_sendmail.
I need to get other columns values of the updated row and I did it using the
first select statement found in the trigger. I find that irrespective of any row
that is being modified, the record pointer moves to the bottom most row if more
than one rows exist. How do I correct this bug? Is there any option other than creating
a temporary table as it would be a tedious procedure. Also, I find SQL7 doesnot contain
mailaddress as a distinct datatype!!!! Please find below the code I have used.
I would greatly appreciate a reply from you.

CREATE TRIGGER statusreminder
ON [tinfo]
FOR UPDATE
AS
declare
@mailid varchar(30),
@wlno nvarchar(10),
@pname nvarchar(120),
@ctime nvarchar(20),
@cmessage varchar(500)
select @mailid=maddress,@wlno=wno,@pname=pname,@ctime=getdate() from [tinfo]
set @cmessage = 'Hello ' + @pname +'This is in response to your registration with us '+ @wlno + 'This mail was sent at ' + @ctime +'Thank you for registering with us.'
EXEC master..xp_sendmail @recipients = @mailid,@message=@cmessage,@subject = 'Status Update...'

vijaykvr
Starting Member

5 Posts

Posted - 2003-04-23 : 01:10:54

quote:

I wrote the following trigger to update a row which in turn sends a mail using xp_sendmail.
I need to get other columns values of the updated row and I did it using the
first select statement found in the trigger. I find that irrespective of any row
that is being modified, the record pointer moves to the bottom most row if more
than one rows exist. How do I correct this bug? Is there any option other than creating
a temporary table as it would be a tedious procedure. Also, I find SQL7 doesnot contain
mailaddress as a distinct datatype!!!! Please find below the code I have used.
I would greatly appreciate a reply from you.

CREATE TRIGGER statusreminder
ON [tinfo]
FOR UPDATE
AS
declare
@mailid varchar(30),
@wlno nvarchar(10),
@pname nvarchar(120),
@ctime nvarchar(20),
@cmessage varchar(500)
select @mailid=maddress,@wlno=wno,@pname=pname,@ctime=getdate() from [tinfo]
set @cmessage = 'Hello ' + @pname +'This is in response to your registration with us '+ @wlno + 'This mail was sent at ' + @ctime +'Thank you for registering with us.'
EXEC master..xp_sendmail @recipients = @mailid,@message=@cmessage,@subject = 'Status Update...'

vijaykvr
Starting Member

5 Posts

Posted - 2003-04-23 : 01:18:01

Dear mohdowais

Thank you so much for the reply.i wanted to post this thanks note and by mistake the original question got posted by mistake.Pls. ignore it.

Have a good day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources