Скачать или смотреть How to Fix PL/SQL Trigger Delete Issues in Your Code

Discover how to resolve common issues with PL/SQL triggers not properly handling delete operations. Learn best practices and see an example to guide you.
---
This video is based on the question https://stackoverflow.com/q/64805014/ asked by the user 'Gabriela OhSatan' ( https://stackoverflow.com/u/5221104/ ) and on the answer https://stackoverflow.com/a/64806381/ provided by the user 'Gabriela OhSatan' ( https://stackoverflow.com/u/5221104/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PL/SQL Triggger not deleting

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Fix PL/SQL Trigger Delete Issues in Your Code

When working with PL/SQL triggers, especially those handling multiple operations like insertions, updates, and deletions, it can be frustrating when, despite extensive testing, one or more parts of the trigger fail to perform as expected.

The Problem

One common issue is a PL/SQL trigger that does not execute the delete operation correctly. An example scenario might look like this:

You have a trigger designed to update a record in a client profile table based on certain changes. However, when attempting to delete a record, the expected changes are not being processed. The trigger seems to work perfectly for insertion and updates but falters during deletion.

Analyzing the Trigger Code

Let’s take a look at the original PL/SQL trigger code that’s giving the user trouble:

[[See Video to Reveal this Text or Code Snippet]]

Issues Identified

Scope of Variables: The :NEW and :OLD identifiers are used incorrectly during deletion. For a delete operation, you should refer to :OLD values rather than :NEW.

Counting Clients: The client existence check only occurs during the insertion event. This may lead to erroneous results when deleting.

Logic Flow: The trigger needs clear paths for handling inserts, deletes, and updates to ensure they function correctly.

The Solution

After reworking some parts of the original trigger, here’s a refined version that works as intended:

[[See Video to Reveal this Text or Code Snippet]]

Key Adjustments

Correct Usage of :OLD: During deletion and updating, use the :OLD variable to retrieve data pertaining to the record being deleted or updated.

Existence Check for Deletes: Added a check to see if the client exists right before the update during deletion, ensuring the trigger is functioning correctly before executing any update.

Clarity in Logic Flow: Each block (inserting, deleting, updating) explicitly handles what it needs to, ensuring no conflicts arise.

Conclusion

By following this detailed approach and applying the recommended changes, your PL/SQL trigger should now handle delete operations effectively. Remember, careful management of :NEW and :OLD variables is essential for achieving the desired functionality. Happy coding!