How to wirte sum data for statement-level triggers

[ximen]

--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

[VolkerBarth]

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)

create view MyView as
select id, all_amt, cast(sum(all_amt) as varchar(100)) as total_amt
from t1
group by id, all_amt

[Breck_Carter]

id,all_amt,total_amt
1,1222.000000,'2444.000000'
1,12.000000,'12.000000'

[VolkerBarth]

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:

create or replace view MyView as
select id, all_amt, cast (sum(all_amt) over () as varchar(100)) as total_amt
from t1


returns

1,1222,000000,2456.000000

1,12,000000,2456.000000

1,1222,000000,2456.000000

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...

[ximen]

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

[chris_keating]

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

[VolkerBarth]

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:)