--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...> wrote:

>
>
>
> > I have table Invoice (which is header table) and InvoiceItem (which is items
> > table) and InvoiceID as foreign key in secound table which is reference first
> > table.
> > Now, I have some fields which is sum or products of some another columns in
> > InvoiceItems which is depend of business logic.
> > So, I was create a trigger and procedure for InvoisItem table:
> >
> > CREATE OR ALTER TRIGGER INVOICEITEMS_A0 FOR INVOICEITEMS ACTIVE
> > AFTER INSERT OR UPDATE POSITION 0 AS BEGIN
> > EXECUTE PROCEDURE SP_ADM_SUMFIELDS('INVOICEITEMS',
> > NEW.INVOICEID); END
> >
> > CREATE OR ALTER PROCEDURE SP_ADM_SUMFIELDS (TABLENAME
> > VARCHAR(30), ID INTEGER) AS BEGIN
> > UPDATE INVOICEITEMS SET SUMCOLUMN = COLUMN1 + COLUMN2 WHERE
> > INVOICEID = :ID;
> > SUSPEND;
> > END
>
> First, as Svein Erling already outlined, you have created logic which would never end.
> Update to InvoiceItems -> invokes Trigger -> calls procedure which updates InvoiceItems -> invokes Trigger -> calls procedure which updates InvoiceItems ...
>
> Are you sure that the SP is supposed to be updating the InvoiceItems table not the Invoices table?
>
> If so, then you can replace the SP with a BEFORE INSERT OR UPDATE trigger
>
> CREATE OR ALTER TRIGGER INVOICEITEMS_BIU0 FOR INVOICEITEMS ACTIVE
> BEFORE INSERT OR UPDATE POSITION 0 AS BEGIN
> New.SUMCOLUMN = New.COLUMN1 + New.COLUMN2;
> END;
>
> If not, you really want to update the INVOICES table!
>
>
> Sean
>

Thanks Sean, that is what I want to do...