Re: Get rownum or rowid in insert URGENTT!!!!

From David Pomphrey --

Hi Peter,

             to follow-up your and Karsten's postings - yes - it seems that the rowid is available in an insert trigger as the after-image so this is fine. (mind you I don't deal with rowids too much for the reasons below) But I interpreted Carlos's original query to be one of needing the functionality of a timestamp. I.e. having the ability to go back to the table and identify rows that had been inserted at any particular time. Sensibly, the only way to track newly inserted tuples would be to refer to an attribute of the tuples themselves. This would also seem to me to be the most elegant.

It would work sending the rowids back to the client process but how would you elegantly handle them from then on? You couldn't really re-type them into a select query, could you - you would be bound to make mistakes and it would be cumbersome. Why not just use a primary key on the table. If the individual tuples are important enough in their own right that they need to be identified later then it seems sensible that they are part of a simple or composite primary key. Indeed, because there is no guarantee that rowids won't change during the lifetime of a tuple, it would seem mandatory to set up a primary key.

Now, if all Carlos needs is the rowids which could be e.g. saved into another table then either your or Karstens (adapted) solutions would work (but with one glitch). But then again the old issue of not being able to absolutely trust rowids to stay the same still comes into play. Another very important issue (more important than the ageing / changing of rowids) of course is "What happens if the insert occurs, then a trigger returns a rowid, but then later on a rollback is issued?". Wouldn't this completely invalidate the rowids reported before (because that tuple was never actually commit 'ed and even worse, that very same rowid may now be pointing to a different tuple that was not only insert 'ed but also committed. --- hmmm- -- makes you think.

So for the reasons above I would tend to stear clear of rowids. They are very useful (sometimes vital if you have a block corruption) but I don't think Carlos should use them here

Hope that informs !

David P

Glasgow
Scotland
U.K.

Peter Laursen wrote:

> > From David P, Glasgow, Scotland, UK
> >
> >
> > Carlos - you probably wouldn'r be able to get the ROWID until you COMMIT
> > 'ed.
> >
>
> HI David
>
> Both Billy Verreynne above and you mentions that you cant get the rowid
> before the insert is commited.
> I think that with <insert into t values(...) returning rowid into my_var> I
> can get the rowid fine.
> What am I missing here?
>
> Peter Laursen