I know how to create calculated fields in queries, but I don’t want that. My question is about getting the calculations in the table and not doing queries. Warning: do not ‘play around’ with SQL UPDATE statements without having a BACKUP of your database, because if the UPDATE does NOT work as expected, there is NO ‘undo’! A TRIGGER will simply execute such an SQL statement when a NEW record is created (and so update a ‘TOTAL’ column in your TABLE when a NEW row/record is created.) Note: SQL UPDATE statements can also be executed using the TOOLS>SQL… menu in Base. See HERE for more info about TRIGGERS using the HSQLDB engine. If the UPDATE statement is executed by a TRIGGER - see HERE - the TABLE-COLUMN ROWS referenced by the UPDATE statement will be updated when the TRIGGER is executed. HERE is some additional info about the SQL UPDATE syntax.
If you wanted to store the total in a field for each new row added, you would need to use a table UPDATE in a TRIGGER (currently available in HSQLDB version 2.x but not the default version 1.8). SELECT “FIELD_1”+“FIELD_2”+“FIELD_3” AS “TOTAL” FROM “YOUR_TABLE”
(Most people would just use a query, since usually there is no need to store the result in your table.) You can use a QUERY or a table UPDATE to do arithmetic calculations.
0 kommentar(er)
