Jim,

> Jason, what is your goal? Do you want programatic access to the
> number of affected rows or a better way to handle conditional
> update else insert?

In general, I just recall doing things in SP's & TG's that I always hear
myself saying, "I wish I knew for sure that hit the records I wanted it to."
It would clean up a lot of places where I now use count selects and then
operation execution. This always gives me an irritating feeling because
being in a multi-user environment the assumption things aren't going to
change between the execution of the two operations isn't dependable.

Another reason is to further promote my position on how the RowsAffected
issue with views can be resolved. If people see the value of this case and
presume it is with a view there, how I am proposing the RowsAffected with
view is a natural fit for this scenario.

Have you seen my rants about the rowsaffected issue? What do you think of
considering RowsAffected for views (and tables) merely the rows walked for
the operation, where non select inserts are considered singleton? That
should only amount to a counter which increments for each actual row pushed
into the "output" stream and worked upon. Cheap and easy with usable
results.

Doing it this way makes views and table exactly consistent in their
behavior, eliminates the need to inventory exactly what the triggers are
doing to underlying tables. I think it would be difficult and useless for it
to try and make sense of the potential mess people could have going on. The
catch to it is anyone implementing triggers on a view for updating would
need to make sure the actions in the triggers taken will properly implement
what is expected. If an update to a view record actually needs to make some
inserts and deletes, those inserts and deletes better make it such that when
that view is queried a gain the row will come up as expected (or at least
reasonably close since some inconsistencies between client and server are
not uncommon when triggers on tables are involved).

Anyway, I guess I'm off and rambling again...

> If it is the later, a REPLACE verb (syntactically parallel to INSERT)
> is a very good solution for the usage and efficiency.

That would be a very nice feature indeed. I would welcome it. However, the
more broad feature I am requesting above would allow this case with great
ease and other more complex things to boot.

For example, you may want to update a record and if it isn't there insert it
there as well as make an update somewhere else as a result of that unique
occurrence. Something that couldn't be detected in the insert trigger alone.
(Rather a contrived case but it is just one sloppy example.)

Thanks for the thoughts!

Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com