Re: using Triggers to update table value after insert

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:01:24 CST