Скачать или смотреть Understanding How to Insert Multiple Records with SQL Triggers in Oracle

Learn how to implement an SQL trigger in Oracle that inserts multiple records into an audit table whenever new entries are added or records are updated.
---
This video is based on the question https://stackoverflow.com/q/71625748/ asked by the user 'Vicky' ( https://stackoverflow.com/u/17266973/ ) and on the answer https://stackoverflow.com/a/71626494/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) 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: How to to insert two records for the new entry into the same column using triggers

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 Insert Two Records for New Entries using Triggers

In the world of databases, maintaining an accurate and detailed log of changes is crucial for data integrity and audit purposes. When working with Oracle SQL, you might encounter situations where you need to log multiple related records for a single operation—in this case, inserting multiple values into an audit table through triggers. If you've found yourself confused on how to implement this, you're not alone. In this guide, we’ll break down how to achieve this effectively.

The Problem

You have an audit_tab created to log transactions from another table called source_det. The aim is to insert entries into this audit table every time you insert or update records in source_det.

Trigger Conditions

You faced two main conditions:

Inputting a record with values for both ref_id and sys_other.

The need to ensure that both corresponding entries are recorded in the audit table when both values are provided.

Current Issues

Your trigger worked fine when inserting a single value but failed to insert both records when both values were supplied. Let’s illustrate this with an example:

Insert single record:

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

Expected output when both ref_id and sys_other are provided:
The goal should be to see two entries logged in the audit table. However, only one was inserted under the current logic.

The Solution

Rearranging the Trigger Logic

To resolve the issue, a simple rearrangement of the conditions in your trigger can make all the difference. You'll want to ensure that the condition checking for the existence of both values is processed before the others.

Revised Trigger Code

Here’s how you can modify your trigger:

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

Testing the Modified Trigger

After implementing the changes, you can test the trigger to confirm its functionality:

Insert a new record:

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

Query the audit_tab to verify:

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

The output should now reflect two separate entries in the audit table, confirming that both values have been recorded as expected.

Conclusion

By properly rearranging the logic in your SQL trigger, you can ensure that multiple records are logged in your audit table when inserting or updating entries in your main table. This not only improves the accuracy of your data logs but also enhances your auditing capabilities.

If you have further questions or need assistance with Oracle SQL, feel free to leave a comment or reach out!