Subject Re: RowsAffected in SP/Trigger
Author Alexander V.Nevsky
--- In IBDI@y..., "Ann W. Harrison" <aharrison@i...> wrote:

> I suppose that you wouldn't agree with arguments like "it's not
> and "system variables are nasty beasts."

I'm not so shure I wouldn't agree, :) I hate globals too. Reason of
my interest to it is: I usually make changes within snapshot
transactions and have'nt problems - lock conflict solves all of them.
But now I started to play with read_commited ones. If I make

Update SomeTable ... Where AttributeCol>Value

and from previous SP statements I know 1 row should be affected, how
can I be shure that during SP execution other transaction did'nt add
and commited one more or changed attribute so my update don't affect
any? Maybe I'm wrong about read_commited?

Replace this simple statement with

For Select ID From SomeTable ... Into :LocVar Do
Update SomeTable Set ... Where ID=:LocVar

is a solution, but

1. I'm not always want update this row, sometimes I want to know, was
it updated or not and make different actions depending on it.
2. I'm lazy... :)

> and can return that value from a stored procedure. A trigger could,
> one supposes, write the value to a statistics table.

It's solution, but expensive.

> System variables are nasty beasts because:
> They don't work well in modular programs and modular is the only way
> to go (my opinion, perhaps, but very correct). What value does the
> SQLUPDATE_COUNT have after you've invoked a procedure that does
> updates then calls another procedure that does five more and calls
> another that does ... and so on?

I shurely shall not be interested in it. I interested in result of
execution of single statement within SP.

Best regards.