ON DELETE trigger - join on old values
<p>I try to log changes to a DB2 table using a delete and update trigger.
Let's assume a simple example: a person has an address. The log table should contain not only person data but also some values from the address table.</p>

<p>So the tables are PERSON (ID, NAME) and ADDRESS(PERSON_ID, STREET, ...)</p>

<p>Here is what I tried to set up:</p>

<pre><code>CREATE TRIGGER log_history
AFTER UPDATE OR DELETE ON person
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
 DECLARE street VARCHAR(150);
 SET street = (SELECT street from ADDRESS join person on ADDRESS.person_id=O.id);
 INSERT INTO LOG(id,name,street) VALUES(O.id, O.name, street);
END
</code></pre>

<p>The updates are being logged perfectly. I see all old values inserted into tha log table, including the street.</p>

<p>For DELETE operations, the log row only contains the values of the PERSON table, but the STREET column is NULL for deleted rows.</p>

<p>I also tried using a BEFORE trigger, and I tried join with a simple where clause. The result is always the same. </p>

<p>I should possibly mention that there is an ON DELETE CASCADE FK that deletes the ADDRESS when you delete a PERSON. My understanding, however, is that the deletion of that dependent table (ADDRESS
) happens after the triggers...</p>

<p>I am using DB2 10.5 LUW on a Linux server (shouldn't be relevant, but...)</p>

<p>So does anybody know how to retrieve joined values from another table than the one triggering the delete? Thx</p>