---------- Original Message -----------
From: Sándor Tamás <sandortamas@...>

> Hi,
>
> It's a great thing to have selectable SPs in Views, but I have a problem.
> According to my tests, is it true that if a view consists a selectable
> SP and not on a simple SQL, it always executes that SP? Because I
> realized that if I make an update on that view (through an update
> trigger), it takes as much time as the original SP to run.
> Before you say that, the trigger simply runs another SP, which takes
> some milliseconds to run, so it can't be the problem.
>
> Am I right that if a view gets it's datas from an SP, it runs it every
> time something changes?

------- End of Original Message -------

When a trigger fires on a view, it's given full NEW.* and OLD.* values,
regardless of what was in your statement. So for "update myview set blah = 3
where id = 2" to work, it has to find the row in the view where id = 2, get the
rest of the fields for OLD and NEW, then call the trigger with NEW.BLAH = 3.
Since it can't know where in the SP's output that row might show up, it has to
start from scratch; and since the output of an SP can't be marked as having a
PK, there could be multiple matching rows and it won't know until it exhausts
the SP's entire output. Views also can't tell an SP "hey, the user wanted id =
2, can you maybe do less work and retrieve just that row?" -- there's no hidden
back-channel of communication there. (Sure would be nice, though.)

Views are dynamic, so Firebird won't be cacheing the result of the SP to use
instantly every time you access the view; your use of "every time something
changes" makes me think you're thinking of materialized tables, which Firebird
doesn't have as a feature (but you can build yourself in various ways -- I have
some incomplete thoughts on the matter at http://www.pseudotheos.com/
view_object.php?object_id=1541#1) By default, a true view fetches its data from
source every time you ask for it. In your case, that means an SP gets run.

Are you in a situation where you're selecting from the view first, then
updating what you got back, and your problem isn't that the SP gets run once,
but that it somehow gets run multiple times? Are you attempting to run update
statements on it directly (as in my example above), or via an open cursor,
where Firebird might be able to use the currently-selected data to run the
trigger, without calling the SP again? (I'm not sure that would help, but I'm
curious.)

-Philip