trigger logic

[michael.kirby]

I have been reading up on triggers and I am still unclear as to the
best way to accomplish the following:

Each row in table A has an account number field. After insert of record
1 into table A, if the value of the acccount number exists in table B,
I need to update that corresponding record in table B, otherwise I need
to insert the record into table B. I know that I need an insert
trigger, so that after each insert into table A, an event is triggered.
What I am unsure about is what type of logic is actually allowed in a
trigger. How would I accomplish the checking to see if the record
exists in table B, if so do an update, otherwise do an insert? What is
tbe best way, in a trigger, to tell if a record with this account
number exists in table B. Thanks.

[Serge Rielau]

mi***********@gmail.com wrote:

I have been reading up on triggers and I am still unclear as to the
best way to accomplish the following:

Each row in table A has an account number field. After insert of record
1 into table A, if the value of the acccount number exists in table B,
I need to update that corresponding record in table B, otherwise I need
to insert the record into table B. I know that I need an insert
trigger, so that after each insert into table A, an event is triggered.
What I am unsure about is what type of logic is actually allowed in a
trigger. How would I accomplish the checking to see if the record
exists in table B, if so do an update, otherwise do an insert? What is
tbe best way, in a trigger, to tell if a record with this account
number exists in table B. Thanks.

Which version of DB2 are you on?
Her eis one way of doing it:
CREATE TRIGGER trg1 AFTER INSERT ON B
REFERENCING NEW_TABLE AS NTAB FOR EACH STATEMENT MODE DB2SQL
MERGE INTO A USING NTAB
ON A.pk = NTAB.pk
WHEN NOT MATCHED THEN INSERT (NTAB.pk, .....)
WHEN MATCHED THEN UPDATE SET A.c1 = NTAB.c1, ....;

In older versions of DB2 (e.g. pre V8.1.2)

CREATE TRIGGER ....
REFERENCING NEW AS n FOR EACH ROW
BEGIN ATOMIC
DECLARE cnt BIGINT;
UPDATE A SET A.c1 = N.c1, ... WHERE A.pk = N.pk;
GET DIANOSTICS cnt = ROW_COUNT;
INSERT INTO A SELECT * FROM TABLE(VALUES (n.pk, n.c1, ..) AS X
WHERE cnt = 0;
END
%
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[michael.kirby]

Thats great. Thanks a lot. Looks like what I need.

[michael.kirby]

I think the answer is no, but would it change anything, if it were
possible for a row to exist multiple times with the same account number
in the table that is being updated? Does the "ON" portion of the MERGE
statement need to be on primary key/? Thanks

[Serge Rielau]

mi***********@gmail.com wrote:

I think the answer is no, but would it change anything, if it were
possible for a row to exist multiple times with the same account number
in the table that is being updated? Does the "ON" portion of the MERGE
statement need to be on primary key/? Thanks

MERG Ecan update multiple traget rows from one source.
But each individual row in the target must be updated/inserted at most once.
That is if you could have dups in your INSERT statement, e.g.:
INSERT INTO A(pk, c1) VALUES (1, 1), (1, 2), (2, 3);
Then you need to aggregate NEWTAB in the USING clause. E.g. by doing a
SUM(c1) GROUP BY pk.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab