Subject Re: [firebird-support] Re: Help with a trigger/procedure
Author Fabio Gomes

So what my procedure will look like?

select sum(qtde) from table into :something
delete from table
insert into table (qtde) values (:something)

Will just it work?


On 9/11/06, Adam <s3057043@...> wrote:
> --- In<>,
> "Fabio Gomes" <fabioxgn@...>
> wrote:
> >
> > Hi guys,
> >
> > Sorry for taking this thread out of the grave, but i have another doubt
> > about the same thing.
> >
> > One of the ideas that poped up in this thread was to make a table
> for the
> > batch numbers and just insert values there, and write a procedure to
> clean
> > up this table once in a while (sum all the rows with the same batch
> number,
> > create just one with the total and after that delete all the old
> records).
> >
> > After dirting my hands with code and hitting the head against the wall
> > severel times, now i think this would be the best way to do it.
> >
> > But still i have a doubt.
> >
> > Lets imagine that my table is filled with data, and i run the
> procedure to
> > sum the records of the same batch number and after that create just
> one row
> > with the total qualtity and then delete all the other records.
> >
> > Wich would be the best way to do it? what if more users are using
> this table
> > when i m running this procedure? how can i prevent my data for being
> > inaccurrate?
> >
> > Could you guys help me out with this?
> Just do the sum, delete, insert single row in a single transaction.
> MGA will take care of the rest for you. If you follow the procedure,
> it is impossible to get inaccurate data.
> Transactions starting prior to your sum procedure transaction
> committing will see the old values of the table (and so get the right
> result). Transactions starting after your sum procedure commits will
> not see the deleted records but will see the single record (+ anything
> since the sum transaction started).
> Adam

[Non-text portions of this message have been removed]