Hello,

I use the following trigger:

CREATE TRIGGER "VERWENDUNG_UPDATE" AFTER INSERT ON "KONTOBUCHUNGEN"
BEGIN
execute immediate ''update verwendung set betrag = SELECT SUM(BETRAG) FROM KONTOBUCHUNGEN WHERE VERWENDUNG =
verwendung.BEZEICHNUNG,
buchungsdatum = current_timestamp()  where exists (select verwendung from kontobuchungen where verwendung = verwendung.bezeichnung)'';
END

The update for betrag works perfect. The update for buchungsdatum update all.
What do I wrong?

EDB 2.02 B3

Please help.
Thanks
Dieter Nagy  

Dieter,

<< The update for betrag works perfect. The update for buchungsdatum update
all.  What do I wrong? >>

Could you possibly email me the database that you're using ?  Catalog and
table files, please, and to timyoung@elevatesoft.com.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
it's on the way.

Thank Tim
Dieter



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Dieter,

<< The update for betrag works perfect. The update for buchungsdatum update
all.  What do I wrong? >>

Could you possibly email me the database that you're using ?  Catalog and
table files, please, and to timyoung@elevatesoft.com.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Dieter,

<<  it's on the way. >>

Okay, I've looked at this in detail, and I'm still a little confused as to
what you're trying to accomplish.  Do you really want to update all of the
totals for the verwendung table after every insert into the KONTOBUCHUNGEN
table ?  I would think that you would only want to update the total for the
appropriate verwendung row.

The issue with your SQL, however, is that the sub-query for the EXISTS:

(select verwendung from kontobuchungen where verwendung =
verwendung.bezeichnung)

always returns at least one row, thus making the EXISTS always be True, and
the SET statements to always execute.  You can see this by running this
SELECT query:

SELECT * from verwendung
where exists (select verwendung from kontobuchungen where verwendung =
verwendung.bezeichnung)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim thanks,

the update for betrag works perfect. How can I do that the row for buchungsdatum where updated.

Thanks
Dieter





"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Dieter,

<<  it's on the way. >>

Okay, I've looked at this in detail, and I'm still a little confused as to
what you're trying to accomplish.  Do you really want to update all of the
totals for the verwendung table after every insert into the KONTOBUCHUNGEN
table ?  I would think that you would only want to update the total for the
appropriate verwendung row.

The issue with your SQL, however, is that the sub-query for the EXISTS:

(select verwendung from kontobuchungen where verwendung =
verwendung.bezeichnung)

always returns at least one row, thus making the EXISTS always be True, and
the SET statements to always execute.  You can see this by running this
SELECT query:

SELECT * from verwendung
where exists (select verwendung from kontobuchungen where verwendung =
verwendung.bezeichnung)

--
Tim Young
Elevate Software
www.elevatesoft.com

Dieter,

<< the update for betrag works perfect. How can I do that the row for
buchungsdatum where updated. >>

Well, I would change the trigger to this:

ALTER TRIGGER "VERWENDUNG_UPDATE"
AFTER INSERT ON KONTOBUCHUNGEN
BEGIN
  DECLARE UpdateStmt STATEMENT;

  PREPARE UpdateStmt FROM 'update verwendung set betrag = SELECT
SUM(BETRAG) FROM KONTOBUCHUNGEN WHERE VERWENDUNG = verwendung.BEZEICHNUNG,
                           buchungsdatum = current_timestamp()
                           where verwendung.bezeichnung = ?';
  EXECUTE UpdateStmt USING NEWROW.verwendung;
END

That way only the one row in the verwendung table that is related to the new
row being inserted is updated.  I would also put this same kind of trigger
on the KONTOBUCHUNGEN table as an AFTER UPDATE trigger and AFTER DELETE
trigger.  Of course, one thing that you may consider is making the update
code into a procedure and just calling it from each trigger with a single
parameter - the NEWROW.verwendung column value.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

many thanks for your help.

Dieter



Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Dieter,

<< the update for betrag works perfect. How can I do that the row for
buchungsdatum where updated. >>

Well, I would change the trigger to this:

ALTER TRIGGER "VERWENDUNG_UPDATE"
AFTER INSERT ON KONTOBUCHUNGEN
BEGIN
  DECLARE UpdateStmt STATEMENT;

  PREPARE UpdateStmt FROM 'update verwendung set betrag = SELECT
SUM(BETRAG) FROM KONTOBUCHUNGEN WHERE VERWENDUNG = verwendung.BEZEICHNUNG,
                           buchungsdatum = current_timestamp()
                           where verwendung.bezeichnung = ?';
  EXECUTE UpdateStmt USING NEWROW.verwendung;
END

That way only the one row in the verwendung table that is related to the new
row being inserted is updated.  I would also put this same kind of trigger
on the KONTOBUCHUNGEN table as an AFTER UPDATE trigger and AFTER DELETE
trigger.  Of course, one thing that you may consider is making the update
code into a procedure and just calling it from each trigger with a single
parameter - the NEWROW.verwendung column value.

--
Tim Young
Elevate Software
www.elevatesoft.com