Sam Durai wrote:
Need help to write a trigger according to the following business
requirement. This on DB2 UDB V8.2 / AIX 5.3
Whenever a 100th record is inserted into my 'ACCOUNT' table with a
particular 'BATCH_ID' I need to either update or insert a row onto
'DESCRIPTION column of STATUS' table as verified for that particular
BATCH_ID.
First, as Knuth indicated in his post, you often get better answers if
you provide what you have done so far. Second, why do you want to record
each 100 update? Would it not be better to record each update and divide
that by 100 when you inspect the table?
Anyhow, can you guarantee that your account's are inserted one by one,
as in:
insert into account (...) values (...) ?
Otherwise I think it will be rather difficult to do what you describe
with one trigger. The best I can come up with involves an extra table
and an extra trigger. The code looks very error prone and I would think
twice before using it in any critical situation:
drop table account @
create table account (
account_id int not null
GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1) primary key,
batch_id int not null
)@
drop table status @
create table status (
batch_id int not null primary key,
description varchar(10) not null
)@
drop table detector @
create table detector (
batch_id int not null primary key,
cnt int not null
)@
drop trigger a @
create trigger a
after insert on account
referencing new as n
for each row
begin atomic
merge into detector d
using (
select batch_id, count(1) from account
where batch_id = n.batch_id
group by batch_id
) X (batch_id, cnt)
on X.batch_id = d.batch_id
when matched then
update set cnt = X.cnt
when not matched then
insert values (x.batch_id, X.cnt);
end @
drop trigger b @
create trigger b
after update on detector
referencing old as o new as n
for each row
when ((n.cnt / 100) (o.cnt / 100))
begin atomic
merge into status s
using lateral(values(n.batch_id, char(n.cnt / 100)))
X (batch_id, description)
on X.batch_id = S.batch_id
when matched then
update set description = X.description
when not matched then
insert values (x.batch_id, X.description);
end @
/Lennart
