On Wed, 21 Jan 2004, Harald Fuchs wrote:
> In article <200401201905.46699.dev@archonet.com>,
> Richard Huxton <dev@archonet.com> writes:
>
> > On Tuesday 20 January 2004 16:42, Tom Lane wrote:
> >> Harald Fuchs <hf118@protecting.net> writes:
> >> > Why? If the underlying table has a primary key, finding corresponding
> >> > pairs is trivial; if there isn't, it's impossible.
> >>
> >> Exactly. Nonetheless, the correspondence exists --- the UPDATE
> >> definitely updated some particular row of the OLD set into some
> >> particular one of the NEW set. If the trigger API makes it impossible
> >> to reconstruct the matchup, the API is broken.
>
> I would not say so. You could use tables without primary keys, and
> you could define statement-level triggers on them, but you could not
> identify a particular row in this very special and probably rare case.
A technique that requires matching of primary key values also undercuts
its usefulness for at least some types of triggers. For example, ON UPDATE
referential actions need three pieces of information, the starting state,
the end state and the mapping between those states. AFAICS you cannot
fake the last by trying to map primary key values and still implement the
constraint correctly. Even if 99.9% of the time the primary key value
doesn't change, you can't safly implement the triggers this way. Other
triggers of the same sort may run into the same problems.
> > Perhaps they should be cursors? The only sensible way I can think of working
> > with them would be:
> > 1. count how many rows affected
> > 2. step through one row at a time, doing something.
>
> When I read about the "insert" and "delete" pseudotables in a book
> about Transact-SQL, i was enthusiastic about the elegance of this
> idea: you're operating on multiple (perhaps lots of) rows, and the SQL
> way of doing that is by set-operations, i.e. single operations
> affecting a set of rows. Pseudotables extend this idea nicely into
> the area of statement-level triggers. Your cursor idea doesn't look
> very SQL-like to me.
>
> We really should find an Oracle/DB2/Informix guy who can tell us how
> to get that right.
It wouldn't surprise me if there was an internal key (or row number)
that could be used to match the rows between the old and new.