Subject Re: [firebird-support] what should i use? trigger, procedure or am i doing something stupid?
Author Fabio Gomes
Thanx, i think i ll use triggers then, anyway its not the design of my
database, this triggers are from something like.. stupid business logic, but
its what my boss want, so i m doing it.

These triggers will be used to add products on stock only, something like..
when you buy a new product you have to add it to the stock and when you are
doing this, you have the option to update the product price directly..
something like this:

I bought some pencils, then you have a form when you fill the price you paid
for the pencils, the other expenses and the software will calc the prices,
and when you finish this process you add this pencil to the "MOV_EST_ITEM"
table, this table is just for historic purposes i dont use this table to
calc stock quantity and stuff like that, this table has a lot of information
about the pencil, cost, expenses, batch number, bar code, etc... so i have
another stock table wich i use on orders and other places, at first you
think it will be always duplicating stuff, it will, but i will not use this
table for stock purpouses, i think that there is too much useless data, so
i m using 2 tables, i think an image is better than words, take a look:

Thats why i need the triggers, to update the products information and to
change the other tables.. it will work like:

When i add the pencils to this table, it will "pull the trigger" that will:

1 - Update the products table with the new data
2 - Add data to the ESTOQUE table
3 - When this happens the ESTOQUE table trigger will add the products to the
LOTE (batch) table and update the total stock for that item.

Thats why i called it "cascating triggers"

I still donĀ“t know if i m doing something stupid, i dont have much
experience in this field, but if i am, i think i ll know soon enough :)

anyway, thanx for the advices.

On 8/4/06, Dimitry Sibiryakov <SD@...> wrote:
> On 3 Aug 2006 at 14:48, Fabio Gomes wrote:
> >I have several tables, like, products, order, stock itens, etc, i ll
> >just give you an example cause i dont have anything certain right now,
> >i m just thinking how to do this.
> >
> >I want to do something like, when i insert or update a stock item, i
> >want to update the products and some other fields. so i wrote this:
> Such desire mean that your database is denormalized. Is this
> denormalization deliberate or comes from bad initial design?
> SPs are as good as triggers, but triggers has one advantage
> comparing with SP: user can't bypass them by direct table access. On
> the other hand you can prevent it with rightly assigned rights...
> --
> SY, Dimitry Sibiryakov.

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