Hi guys,

Sorry to bother you again with my questions but i want to do the right
thing, and i m not sure if what i m doing is going to work.

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:

CREATE TRIGGER MOV_EST_ITEM_AI0 FOR MOV_EST_ITEM
ACTIVE AFTER INSERT POSITION 0
AS
declare variable PCUSTO varchar(1);
declare variable PVENDA varchar(1);
declare variable PCOMPRA varchar(1);
begin
select VALOR FROM CONFIGS WHERE CHAVE='mov_est_atu_pcusto' into:PCUSTO;
if(PCUSTO = 'S') then
begin
update produtos set pro_pcusto = new.mei_pcusto where pro_cod =
new.pro_cod;
end

select VALOR FROM CONFIGS WHERE CHAVE='mov_est_atu_pcompra' into:PCOMPRA;
if(PCOMPRA = 'S') then
begin
update produtos set pro_pcompr = new.mei_valor where pro_cod =
new.pro_cod;
end

select VALOR FROM CONFIGS WHERE CHAVE='mov_est_atu_pvenda' into:PVENDA;
if(PVENDA = 'S') then
begin
update produtos set pro_pvenda = new.mei_pvenda where pro_cod =
new.pro_cod;
end

INSERT INTO ESTOQUE (MEI_COD, PRO_COD, EST_TIPO, EST_LOTE, EST_QTDE)
VALUES
(NEW.mei_cod,NEW.pro_cod, 'F', new.mei_lote, new.mei_qtde);
end


What the code above does is:

When i m inserting a value into a table, it will do 2 things:

First check my configs table and depending on the config it will update the
PRODUTOS (products) table with the value that the user is inserting, and
after that insert a new row into my stock (estoque) table, and in my ESTOQUE
(stock) table i have another trigger that will insert some stuff in my
"batch" table....

So, would it be better if i write a procedure that do the whole thing
instead of these "cascading" triggers? Is it going to work?

I never worked with this kind of database programming, so i m a bit lost,
hope you guys can give me some advices.

Oh, and other question, i don´t know if i should ask it here on in a C#
forum, but, i m using a transaction in my C# code, and using a rollback() if
it fails, if i do a rollback() in my C# code, it is going to rollback
everything my procedure and triggers did isnt it? or should i code the
transaction inside the procedure?

thanx in advance,

-Fábio.


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