Skip to Main Content
IBM Power Ideas Portal


This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas
  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Status Delivered
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Feb 22, 2021

Allow triggers to fire on records updated or deleted by Referential Integrity CASCADEs

Currently, when a dependent table record is deleted or updated because of a referential integrity CASCADE caused by the delete or update on a parent table, triggers do not fire on that dependent table.

Please allow an option to fire off triggers on a dependent table record deletion or update caused by a referential integrity CASCADEd delete or update. (perhaps also on SET NULL or SET DEFAULT - really, any RI action that changes or deletes data in a dependent row).

Something like:

FOREIGN KEY (...)
REFERENCES MyTable (...)
ON DELETE CASCADE WITH TRIGGERS


Use Case:

I am working on a project now, modernizing a database for one of my client's major applications. I want to define data relationships in the new normalized files, which means using referential integrity.

The client, however, wants all actions on the new data logged to log files (containing record images).
This means using triggers to capture all changes to the data. It also means using an *AFTER *DELETE trigger to capture the image of a deleted record (the modernized application will have savepoint restoration, so these images are important). If I use RI with cascaded deletes, I cannot use a trigger. So, to use the triggers, I cannot use RI, and I instead have to use triggers to simulate RI, using a handcoded definition of the database relationships.

I do not understand why triggers do not fire on RI initiated data changes on dependent files; please allow the option to fire them.


Idea priority Medium
  • Guest
    Reply
    |
    Apr 24, 2021

    While the IBM i database team does not intend to provide the enhancement you suggest, we have a solution to your logging requirement called system period temporal tables. This was introduced in 7.3 and records all data changes in a history table. If you need full insight into deletion of rows, use the ON DELETE ADD EXTRA ROW option. Setup is easy. You need to add a few new required columns to your table and enable it. The database handles all the updates to the history table as your data changes. You can query the history table to see any prior version of your data.

    Using journals, as mentioned by others in the RFE discussion, is another great alternative to let the system capture the changes for you.

  • Guest
    Reply
    |
    Mar 2, 2021

    The idea for this RFE is acceptable, but Paul (pnicolay) is correct. Journals are quite efficient and can do a complete job logging all database changes (those through cascaded triggers and any other updates). Triggers could do this too, but I believe the performance of journals would be better.

  • Guest
    Reply
    |
    Feb 23, 2021

    For logging database transactions you can use journals.