Re: using Triggers to update table value after insert

yeah, I think your correct Mark...I've already had a few people telling me this and I'm beggining to get the point ;O)

At the moment I'm simply creating a new table based on the sql statement.

i.e
create table tbl_totvol as select sum(volume), billingid from xact where billingid = '$searchstring'

Anyway, thanks for you input, it's most appreciated ;O)

have a nice weekend

Nicky Mudie
Apps Engineer
Chello Broadband
Amsterdam

"Mark Wagoner" <mwagoner_at_iac.net> wrote in message news:975u05$mf1$1_at_genma.iac.net...
> You can't select on the table that the update is occurring on, you will
 get
> the infamous "table is mutating" error. I'm also not sure the update in
 the
> trigger will work, since it will fire the trigger again causing a
 recursion
> problem.
>
> You are probably better of creating a different table which SQL*Loader
> inserts into. This table then has a trigger which updates a corresponding
> row in a second table. The first table can be truncated before (or after)
> each run of SQL*Loader since you don't need the info once the trigger has
> done its job.
>
> "Nicholas Mudie" <nmudie_at_chello.com> wrote in message
> news:0vul6.1753$Ed3.439374_at_amsnews02.chello.com...
> > Hello folks,
> >
> > My case:
> >
> > I have this .cvs file which gets filled every hour full of LDAP
 information.
> >
> > Then I use SQL LOADER DBI within a perl script, run this from a crontab
 on
> > solaris which fills the ORACLE table with this new information.
> >
> > Now, lets say the .csv file contains 2 fields values
> >
> > billingid volume
> > 320 1000
> >
> > so the oracle table looks like the above eample.
> >
> > Now, lets say an hour later that the following information is to be
 inserted
> > into the table:
> >
> > billingid volume
> > 320 999
> >
> > I don't want the oracle table to look like this:
> >
> > billingid volume
> > 320 1000
> > 320 999
> >
> > I want it to look like this:
> > 320 1999
> >
> > so, as you can see I want Oracle to recognise that the billingid is the
 same
> > before it inserts itself into the table and then do an addition on the
> > volume values.
> >
> > I have written a trigger which I hoped would work but I'm getting
> > compilation errors, please see below:
> >
> > CREATE OR REPLACE TRIGGER Trig1
> > BEFORE INSERT ON xact
> > FOR EACH ROW
> > DECLARE
> > vsum NUMBER;
> > BEGIN
> > SELECT SUM (volume)
> > INTO vsum
> > FROM xact
> > WHERE billingid = :new.billingid;
> > BEGIN
> > UPDATE xact SET :old.volume = vsum
> > WHERE billingid = :new.billingid
> > END;
> > END;
> >
> > what's wrong here then????
> >
> > It seems so simple to do..
> >
> > Nick
> > Apps Engineer
> >
> >
> >
> >
> >
> >
> >
>
>
Received on Fri Feb 23 2001 - 09:58:16 CST