using Triggers to update table value after insert

From: Nicholas Mudie <nmudie_at_chello.com>
Date: Fri, 23 Feb 2001 14:31:24 GMT
Message-ID: <0vul6.1753$Ed3.439374@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 - 08:31:24 CST