Database stored proc and triggers

When working with databases, stored procedures, and triggers are powerful tools that allow developers to automate and streamline data operations. Let us delve to explore both of these database objects in detail, covering their benefits, and use cases, and providing examples of how they are implemented in SQL.

1. Pre-requisite

To understand this article, we need a database, but setting one up can often be tedious. However, with Docker, it becomes much simpler. You can watch this video to learn how to install Docker on Windows. Once installed, open the terminal and run the command below to set up and launch PostgreSQL.

-- Remember to change the password –
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to set a password of your choice. If everything goes well, the PostgreSQL database server will be running on port 5432, and you can connect to it using the DBeaver GUI tool.

2. What is a Stored Procedure?

A stored procedure is a precompiled set of one or more SQL statements that can be executed as a single unit. Stored procedures help in encapsulating repetitive and complex SQL queries, enabling better performance and maintainability. They can accept input parameters, execute complex logic, and return results.

2.1 Advantages

• Performance: Since stored procedures are precompiled, they run faster than sending raw SQL queries from the application.
• Reusability: The same procedure can be used across multiple applications or users, avoiding code duplication.
• Security: Stored procedures can provide an extra layer of security by limiting direct access to the database tables and allowing execution rights to specific users.

2.2 Code Example

Let’s look at an example of a simple stored procedure in a PostgreSQL database. This procedure retrieves a list of employees based on a department:

-- create or replace the procedure
create or replace
procedure get_employees_by_department(dept_id int)
language plpgsql
as $
declare
    emp record;

begin
-- loop through the result set and display with raise notice
    for emp in 
        select
	employee_id,
	first_name,
	last_name,
	salary
from
	employees
where
	department_id = dept_id
    loop
        raise notice 'employee id: %, name: % %, salary: %',
	emp.employee_id,
	emp.first_name,
	emp.last_name,
	emp.salary;
end loop;
end;

$; 

2.2.1 Code Explanation

The code starts by creating or replacing a stored procedure called get_employees_by_department, which accepts an integer input parameter dept_id. This parameter represents the department ID we want to retrieve employee details. The procedure is written in the PostgreSQL procedural language PL/pgSQL, which is specified by the LANGUAGE plpgsql declaration.

Inside the procedure, a variable emp of type RECORD is declared. This variable temporarily stores the result of each row fetched from the employees table. The executable part of the procedure starts with the BEGIN block, followed by a FOR loop. This loop iterates over all rows returned by the SELECT statement, which retrieves the employee_id, first_name, last_name, and salary for employees that belong to the specified department (where department_id matches dept_id).

During each iteration of the loop, the procedure uses RAISE NOTICE to print out the employee’s details, including their ID, first name, last name, and salary. The placeholders % in the RAISE NOTICE statement are replaced by the values from the current row stored in the emp variable. After all rows have been processed, the loop ends with END LOOP, and the procedure completes with the END block.

2.3 Code Execution and Output

Once the stored procedure is created, it can be executed like this:

-- call the procedure
call get_employees_by_department(1);

This would print a list of employees where the department_id is 1.

3. What is a Trigger?

A trigger is a special type of stored procedure that is automatically executed (or “triggered”) in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Triggers are commonly used to maintain data integrity, audit changes, or automatically log historical data.

3.1 Advantages

• Automation: Triggers allow the database to automatically respond to certain events without needing manual intervention.
• Data Integrity: They help in enforcing business rules, ensuring that certain operations are automatically performed to maintain consistency.
• Auditing: Triggers can automatically log changes to critical tables, providing a detailed history of actions.

3.2 Code Example

Here is an example of a trigger in PostgreSQL that logs changes made to the employees table whenever an update occurs:

create or replace
function log_salary_change() 
returns trigger 
language plpgsql
as $
begin
-- Insert old and new salary into the employee_audit table whenever an update happens
    insert
	into
	employee_audit (employee_id,
	old_salary,
	new_salary,
	updated_at)
values (OLD.employee_id,
OLD.salary,
NEW.salary,
CURRENT_TIMESTAMP);

return new;
end;

$;

-- Create the Trigger on the Employees Table
create trigger trigger_employee_salary_update
after
update
	of salary on
	employees
for each row
execute function log_salary_change();

3.2.1 Code Explanation

The code defines a:

• AFTER UPDATE – This specifies that the trigger will be executed after an update on the employees table.
• FOR EACH ROW – This means that the trigger will run for each updated row.
• OLD and NEW – These are special keywords that refer to the old (before update) and new (after update) values of the updated row.
• NOW() – A function that returns the current timestamp.

3.3 Code Execution and Output

Once the trigger is created, it can be executed by updating an employee’s salary (trigger will fire):

-- Update the Salary of an Employee (this will trigger the audit log)
update
	employees
set
	salary = 70000.00
where
	employee_id = 1;

Once done verify that the trigger logged the change.

-- Check the Employee Audit Table to Verify the Salary Change is Logged
select
	*
from
	employee_audit;

3.4 When to Use Triggers?

Triggers are useful in situations where you need automatic actions to take place when data changes. For example:

• Automatically recording changes for auditing purposes.
• Maintaining derived or summary tables that depend on the main table.
• Enforcing business rules, such as preventing specific kinds of updates.

4. Conclusion

Both stored procedures and triggers play an important role in modern database management by enhancing performance, security, and automation. While stored procedures are explicitly called by users or applications, triggers are automatically invoked in response to table events. Using these tools appropriately can greatly improve the efficiency and reliability of database operations.

5. Download the code

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.