469,572 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

Updating changed row via trigger in SQL Server 2000?

Hi All,

I'm a relatively newbie to SQL Server 2000, having come from a MySQL
background.

I'm creating my first Trigger statement on a table, and I'd like to
know how I go about performing an update on the row that was changed
when the trigger was fired.

To explain, I have 2 columns, one which contains a member number, the
other which contains a flag that is supposed to indicate whether or
not the member number in the row has changed since the last time the
table was processed for updates.

So, whenever the value in the member number field [memnum] is updated,
I want to set the flag [igproc] to true.

The best I've been able to do is:

CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]
FOR UPDATE
AS
declare @key as int

IF UPDATE (memnum)
select @key = recid from inserted
UPDATE dd_testtable set igproc=1 where recid=@key

This seems to work, but I'd like to know if there's a better way of
retrieving the recid value of the changed row to pass to the UPDATE
statement? Also, I read somewhere in passing that using SELECT
statements and variable assignments within triggers can cause problems
when called from other applications; in this case it will either be a
web site using ASP.or an application developed in FOXPRO. I can't find
where I read this originally, so it's entirely possible I imagined it
or misunderstood it, but I'd very much appreciate it if someone could
confirm whether or not this is the case?

Many, many thanks in advance!

Much warmth,

Murray
Jul 20 '05 #1
2 10420
On Wed, 04 Feb 2004 18:56:18 GMT, M Wells
<pl**********@planetthoughtful.org> wrote:

[ here there be snippage ]
Also, I read somewhere in passing that using SELECT
statements and variable assignments within triggers can cause problems
when called from other applications; in this case it will either be a
web site using ASP.or an application developed in FOXPRO. I can't find
where I read this originally, so it's entirely possible I imagined it
or misunderstood it, but I'd very much appreciate it if someone could
confirm whether or not this is the case?


Just a note to say that I discovered the SET NOCOUNT ON setting that
seems to satisfy my concerns regarding data returned to a calling
application.

Much warmth,

Murray
Jul 20 '05 #2
M Wells <pl**********@planetthoughtful.org> wrote in message news:<2g********************************@4ax.com>. ..
Hi All,

I'm a relatively newbie to SQL Server 2000, having come from a MySQL
background.

I'm creating my first Trigger statement on a table, and I'd like to
know how I go about performing an update on the row that was changed
when the trigger was fired.

To explain, I have 2 columns, one which contains a member number, the
other which contains a flag that is supposed to indicate whether or
not the member number in the row has changed since the last time the
table was processed for updates.

So, whenever the value in the member number field [memnum] is updated,
I want to set the flag [igproc] to true.

The best I've been able to do is:

CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]
FOR UPDATE
AS
declare @key as int

IF UPDATE (memnum)
select @key = recid from inserted
UPDATE dd_testtable set igproc=1 where recid=@key

This seems to work, but I'd like to know if there's a better way of
retrieving the recid value of the changed row to pass to the UPDATE
statement? Also, I read somewhere in passing that using SELECT
statements and variable assignments within triggers can cause problems
when called from other applications; in this case it will either be a
web site using ASP.or an application developed in FOXPRO. I can't find
where I read this originally, so it's entirely possible I imagined it
or misunderstood it, but I'd very much appreciate it if someone could
confirm whether or not this is the case?

Many, many thanks in advance!

Much warmth,

Murray


Triggers in MSSQL fire per-statement, not per-row, so your solution
isn't suitable for the case where many rows are updated at once. If I
understand your requirements, then something like this may be what
you're looking for:

CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]
FOR UPDATE
AS
begin

if @@rowcount = 0
return

IF UPDATE (memnum)
update dd_testtable
set igproc = 1
where rec_id in
(select i.rec_id from inserted i
join deleted d on i.rec_id = d.rec_id
where i.memnum <> d.memnum)
end
go
Simon
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by ChrisL | last post: by
33 posts views Thread by coosa | last post: by
14 posts views Thread by el_sid | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.