Effective Use of PL/SQL Triggers for Dynamic Salary Calculations

Описание: Learn how to create an efficient `PL/SQL` trigger to automatically update salary components in an Oracle database when the basic salary is modified.
---
This video is based on the question https://stackoverflow.com/q/62227096/ asked by the user 'Vipul Sharma' ( https://stackoverflow.com/u/13613347/ ) and on the answer https://stackoverflow.com/a/62229643/ 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: trigger in PL/SQL

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.
---
Unlocking the Power of PL/SQL Triggers for Salary Updates

Triggers are a powerful feature in PL/SQL that allow you to automatically perform actions based on events in a database. In this post, we will dive into how to create a trigger that dynamically updates compensation components in your Oracle database when an employee's basic salary changes.

The Problem Overview

Imagine you have a payroll management system where employee salaries are structured into several components, such as basic salary, dearness allowance (DA), and gross salary. When you update an employee's basic salary, you need to reflect changes not only to the gross salary but also to capture the previous basic salary for historical data purposes.

You have:

Employee Table: Holds current salary information.

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

da is calculated as basic * (5.0/100).

gross is the sum of basic and da.

Salary History Table: Stores old salary information.

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

The Solution: Creating the Trigger

To efficiently manage salary updates while ensuring that historical data is preserved, you need to create a trigger that fires before an insert or update operation on the employee table.

Step-by-Step Guide

Here’s how you can implement this in PL/SQL:

Define the Trigger: This trigger will automatically calculate da and gross, while also saving the previous basic salary into the sal_hist table.

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

Explanation of the Code

Trigger Declaration: The trigger trg_biu_emp is created to listen for insertions or updates on the employee table.

Insert Historical Data: The line INSERT INTO sal_hist(eno, sys_dt, old_basic) VALUES (:new.eno, SYSDATE, :old.basic); stores the previous basic salary whenever an update is made.

Calculating DA and Gross:

:new.da := :new.basic * 5 / 100; computes the new dearness allowance.

:new.gross := :new.basic + :new.da; updates the gross salary accordingly.

Verifying the Trigger Functionality

After implementing the trigger, you can test its functionality with a series of SQL commands.

Example 1: Updating Basic Salary

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

Check the contents of the employee and sal_hist tables:

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

Example 2: Inserting a New Employee

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

Again, query both tables to see results.

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

Conclusion

Using PL/SQL triggers effectively enhances the integrity and accuracy of your database's payroll system by automating salary calculations and preserving historical data. Now you can easily manage employee salary updates without compromising on accuracy.

By implementing such a trigger, you ensure streamlined data handling and maintain operational efficiency within your organization. Happy coding!