ON DELETE trigger - join on old values

Question

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.

So the tables are PERSON (ID, NAME) and ADDRESS(PERSON_ID, STREET, ...)

Here is what I tried to set up:

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

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

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

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

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...

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

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

Your understanding seems incorrect. The AFTER triggers fire "after the changes caused by the actual update of the subject table are applied to the database" - a quote from the manual. Note "the database" vs. "the table". This clearly includes cascaded actions "caused by the actual update of the subject table". — mustaccio
mustaccio, as I stated, I also tried a BEFORE trigger with the same result. If I understand correctly, you suggest trying an "NO CASCADE BEFORE UPDATE OR DELETE". I will try. I think I also tried but had trouble adding the trigger with a message I didn't really understand. I'll keep you updated on my results. Thanks for commenting — Joachim Tuchel
It seems the NO CASCADE BEFORE option works. Now I have another problem: I am using a clever persistence framework in my program that determines that the address object needs to be deleted as well, so if I wanted to go with the TRIGGER solution, I'd have to fight the framework with its caching and object mapping and such, and this sounds like a fight in which you can only lose. — Joachim Tuchel

Joachim Tuchel Joachim Tuchel · Accepted Answer · 2016-05-18T09:16:08

As suggested by mustaccio, NO CASCADE BEFORE DELETE OR UPDATE will allow me to retrieve joined values that were referenced by the deleted row. Unfortunately, I cannot follow that route because the persistency framework we are using is "clever enough" to issued delete statements for the address table before deleting the person. Changing our O/R mappings and teach the framework additional tricks for this special logging purpose seems like a risky endeavour. It seems like implementing the logging behaviour on the program side is comaparable effort-wise and less risky. So I decided to not go for the TRIGGER at all.

No matter what, thanks mustaccio for your comment and pointer.

ON DELETE trigger - join on old values

2 Answers