Trigger help

[Sam Durai]

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.


Any help is greatly appreciated.


Thanks,
Sam

[Knut Stolze]

Re: Trigger help

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.

Have you actually tried something? If so, what? And why did it fail?

CREATE TRIGGER t
AFTER INSERT ON ...
FOR EACH STATEMENT
WHEN ( MOD( ( SELECT COUNT(*) FROM ... WHERE batch_id = ... ), 100) )
MERGE ...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Lennart]

Re: Trigger help

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

[--CELKO--]

Re: Trigger help

>Whenever a 100th record [sic] 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. <<

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

2) Because you do not understand that rows are NOT records, you are
looking for file system solution that would materalize the fact via
procedrual code. Think in terms of tables instead and use a VIEW that
is always correct.

CREATE VIEW Account_100s (..)
AS
(SELECT *, rn
FROM (SELECT .. -- list columns in production code
ROW NUMBER()
OVER(PARTITION BY acct_nbr ORDER BY posting_date) AS rn
FROM Accounts) AS A (.., rn)
WHERE MOD(rn, 100) = 0;

3) Read ISO-11179 rules for data element names. Status is a kind of
attribute (martial _staus, shipping_status , , etc.) and not an
entity. Likewise a description has to describe something in
particular, etc.