Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Trigger and update |
Sun, Nov 30 2008 4:13 AM | Permanent Link |
Dieter Nagy | 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 |
Mon, Dec 1 2008 10:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Dec 1 2008 12:24 PM | Permanent Link |
Dieter Nagy | 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 |
Tue, Dec 2 2008 2:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Dec 3 2008 12:20 PM | Permanent Link |
Dieter Nagy | 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 |
Wed, Dec 3 2008 1:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Dec 3 2008 1:56 PM | Permanent Link |
Dieter Nagy | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |