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