Highlighted
Anonymous_User Absent Member.
Absent Member.
1027 views

AS400 DB2 Triggers

I am looking at trying to make one of my users happy, can anyone help?
🙂

I'm a little wary about just trying this even in our TEST environment
(sits on the same box as our production) and am curious on any comments.

What I have is two tables, we'll call one Header and one Lines.

In table Header we have a column called status and we have an order
number.
In the table Lines, we have several different columns, one of which is
the order number.

What I want to be able to do is to have a trigger fire before the
Header Status column goes above a certain value, it would then copy all
of the rows with that order number in to a different table in a
different database on the same server.

Essentially I'd be doing something like this:
INSERT INTO otherdb.line (SELECT * FROM originaltable WHERE
originaltable.ordernumber = <ordernumber>)

From the IBM documentation it looks like I could do something like:

CREATE TRIGGER copylines
BEFORE UPDATE OF status ON header
REFERENCING OLD AS oldrow
FOR EACH ROW
WHEN (oldrow.status > 59 AND oldrow.status < 70)
INSERT INTO otherdb.line (SELECT * FROM lines WHERE lines.ordernumber
= oldrow.ordernumber)

Does anyone know if the DB2 triggers are synchronous? Anyone ever
played around with using triggers to pass data to Java apps, if the
above pans out, then I would be tempted to throw together a Java app
that gets called that will update our datawarehouse or kicks off
reports at certain order statuses.

Thanks,
Scott


Labels (1)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.