Re: dbms_alerts on multiple updates

Store the xml document in the database. See asktom.oracle.com and seach for xml document. I know Sean Dillion put a bunch of info in there about xml documents. I think there were 3 options depending on your needs. What you are trying to do is ask the database to somehow (via an external mechanism - Delphi program) keep an external thing updated. Much easier to let the database handle the whole thing.

Another way would be to have a last updated field and use the alert to tell you when an update has occured and just have the application ask for all records that are later than the last query you did. eg
app queries today at 1:05 PM and 30 seconds. It gets an alert a minute later.
It asks for all records that had an updated of after 1:05 and 30 seconds and less than sysdate (now) (remember now for the next cycle)

Or have the trigger create a job for every row that was updated. (see dbms_job and see Tom's site for info on this concept) The job would send the info by via a pipe or an entry in a table (rowid) so your app can read it. The job will only run once a commit is done. Which is what you want. Why? If a rollback is done you don't want to be falsly think the record was updated.

Jim

"MikeH" <mike.hammett_at_associates-wheatley.co.uk> wrote in message news:bi5e58$hm7$1_at_titan.btinternet.com...
> Thanks for your input Jim, do you mean I can not achieve my solution at
all,
> or that I cannot achieve it the way that I have set out, if you mean the
> latter, do you have any other ideas as to how I may achieve this?
>
> Thanks
>
> MikeH
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:eVq1b.170256$cF.57935_at_rwcrnsc53...
> > You cannot do it the way you want.
> > Jim
> > "MikeH" <mike.hammett_at_associates-wheatley.co.uk> wrote in message
> > news:bi5c9b$hok$1_at_hercules.btinternet.com...
> > > Thanks for the reply Jim, the only problem is that I would like the
> > trigger
> > > to fire only after commit, this will ensure that I am writing the new
> > record
> > > data to the XML document. I have tried using pipes already , however
I
> > > understand that dbms_pipes are not transactional and therefore they
> donot
> > > fire after commit.
> > >
> > > Thanks
> > >
> > > "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote
in
> > > message news:liq1b.172933$It4.83662_at_rwcrnsc51.ops.asp.att.net...
> > > > "MikeH" <mike.hammett_at_associates-wheatley.co.uk> wrote in message
> > > > news:bi593u$8tp$1_at_titan.btinternet.com...
> > > > > Hi,
> > > > >
> > > > > I want my Delphi application to be able to detect updates on a
> > > particular
> > > > > table and then write the record to an XML document. At the moment
I
> > am
> > > > > using dms_alert to notify my application everytime an update is
> made,
> > my
> > > > > application then proceeds to write out the XML document.
> > > > >
> > > > > This works fine when I update only one record at a time, however
> when
> > > > > multiple records are updated my Delphi application is unable to
> detect
> > > all
> > > > > the alerts. I appreciate that I may be able to use some form of
> > > > > multi-threading, but I would be interested to know if there is
> another
> > > > > Oracle package that will help me acheive this.
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > You could put the rowid in a pipe. see pipes.
> > > > Jim
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Fri Aug 22 2003 - 11:36:30 CDT