473,249 Members | 1,864 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,249 software developers and data experts.

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 10615
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bill Tepe | last post by:
I have a need to insert rows into an Audit type table when values change in certain fields in a table. I thought I could do this via a trigger. However, on requirement is to include in the audit...
3
by: vishnu mahendra | last post by:
I have two table both say A and B. If i insert a record in A that record should be inserted in B. If i delete a record in A that record should be deleted from B. Is that possible.If yes please...
5
by: Neil Rutherford | last post by:
During testing of an application, i noticed a difference between SQL 2000 and SQL 7, both with identical config. In a nutshell: A table has a trigger for UPDATE and DELETE. When a column in the...
1
by: ChrisL | last post by:
I am trying to update text fields where the field only has the value "closed." So a field that has closed and anything else will not be updated. update dbo.Hist set dbo.Hist.Notes = '' FROM ...
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
3
by: Tc | last post by:
Hi, I was curious, I am thinking of writing an application that loads a dataset from a database that resides on a server. The question I have is this, if multiple copies of the app will be...
14
by: el_sid | last post by:
Our developers have experienced a problem with updating Web References in Visual Studio.NET 2003. Normally, when a web service class (.asmx) is created, updating the Web Reference will...
6
by: Rico | last post by:
Hello, I'm creating an audit table and associated triggers to be able to capture any updates and deletes from various tables in the database. I know how to capture the records that have been...
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.