--Create a table CREATE TABLE t1 ( id integer NOT NULL, all_amt numeric , remarks char, ); --insert data INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,1222.000000,null) INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,12.000000,null) INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,1222.000000,null) --Want to use a statement-level triggers, at the new insert data total the same ID's all_amt sum, and update the total results to remarks column --I need the results as follows:: select * from t1 id all_amt remarks 1 1222.000000 2456 1 12.000000 2456 1 1222.000000 2456 asked 13 Jul '14, 11:00 mfkpie8 |
Volker is correct to note that a trigger is not the best approach for this requirement. If you believe that this is required, the easiest trigger to manage this is a row level trigger. A statement level trigger has to account for the possiblity that the insert is not a single row insert i.e., insert into t1 select * from t2. That will require some additional work in the context of a statement level trigger - not much but it has to be handled. Here are examples (with limited validation but should generally work) of row and statement level triggers to handle what you described as output. Row Level Trigger: ALTER TRIGGER "tr_calc_sum_amts_for_id_row_level_trigger" AFTER INSERT ORDER 1 ON "DBA"."t1" REFERENCING NEW AS new_name FOR EACH ROW BEGIN declare sum_all_amts_for_id numeric; select sum( t1.all_amt ) into sum_all_amts_for_id from t1 where id = new_name.id; update t1 set remarks = cast( sum_all_amts_for_id as char(50)); END Statement Level Trigger: ALTER TRIGGER "tr_calc_sum_amts_for_id_statement_level_trigger" AFTER INSERT ORDER 1 ON "DBA"."t1" REFERENCING NEW AS new_name FOR EACH STATEMENT BEGIN declare curs_ids cursor for select distinct id from new_name; declare curr_id integer; declare sum_all_amts_for_id numeric; open curs_ids; lp: LOOP fetch next curs_ids INTO curr_id; if sqlcode <> 0 then leave lp end if; select sum( t1.all_amt ) into sum_all_amts_for_id from t1 where id = curr_id; update t1 set remarks = cast( sum_all_amts_for_id as char(50)) where id = curr_id; end loop; close curs_ids; END answered 14 Jul '14, 11:48 Chris Keating Chris, w.r.t. to the row-level trigger: If you limit the calculated sum to rows with the current id (new_name.id), you might also filter the "update t1 ..." statement with an identical WHERE clause. (Yes, we still do not know whether grouping by id is a requirement here...) Besides that, it might be noted that updating t1 within an AFTER UPDATE trigger will - by definition - fire that trigger recursively (and for the row-level form, the second time for all according rows with the same id). However, it will only run twice as the second calculation of the sum should give the same result, and therefore a third run will be prevented since AFTER UPDATE triggers do not get fired when the data has not modified by the UDDATE statement. AFAIK, that is:)
(14 Jul '14, 12:38)
Volker Barth
|
In general, you will use a query or possibly a view to generate the total amount dynamically instead of storing that within each row (which would certainly violate normalization rules), such as the following (untested)
Breck, thanks for the test - that's why I had written "(untested")...
Apparently my first attempt with a group by has failed (and that should have occurred to me!), well, I'd blame it on the World Cup final (hooray!)...
Here's a tested version with a window function:
returns
As the sample data does only contain ids with the same value 1, it's difficult to tell whether the query should group by id or not...
Thanks Volker Barth, but I wanted to use the principle of the trigger to automatically written to remark it, of course, I am here is just an example but the effect of need is like this