RDBMS Stored Procedures and Triggers

Stored Procedures and Triggers in RDBMS

As databases become larger and more complex, many tasks need to be performed repeatedly. For example, a company may generate monthly reports, update inventory after every sale, or record changes made to employee information.

Instead of writing the same SQL statements again and again, an RDBMS provides stored procedures and triggers to automate these tasks.

A stored procedure is a collection of SQL statements saved inside the database that can be executed whenever needed. A trigger is a special type of program that runs automatically when a specific database event occurs.

Both features help reduce repetitive work, improve efficiency, and keep data consistent.

What is a Stored Procedure?

A stored procedure is a pre-written set of SQL commands stored in the database. Instead of writing the same SQL code every time, you simply call the stored procedure.

Think of it as a reusable function in programming. You write it once and use it whenever needed.

Why Use Stored Procedures?

Stored procedures provide several benefits:

• Reduce repeated SQL code.
• Improve application performance.
• Simplify complex database operations.
• Keep business logic inside the database.
• Make maintenance easier.

They are commonly used in banking systems, payroll software, inventory management, and reporting applications.

Creating a Simple Stored Procedure

The following example creates a stored procedure that displays all student records.

CREATE PROCEDURE GetStudents
AS
SELECT *
FROM Students;

To run the stored procedure:

EXEC GetStudents;

Instead of writing the SELECT statement every time, you simply execute the procedure. This saves time and keeps your code organized.

Stored Procedure with Parameters

Stored procedures can also accept input values. This makes them more flexible and reusable.

CREATE PROCEDURE GetStudentByID
    @StudentID INT
AS
SELECT *
FROM Students
WHERE StudentID = @StudentID;

To display a specific student:

EXEC GetStudentByID 101;

This procedure returns only the record for Student ID 101.

What is a Trigger?

A trigger is a special SQL program that runs automatically when certain events occur in a table.

Unlike a stored procedure, you do not execute a trigger manually. The database automatically runs the trigger whenever an INSERT, UPDATE, or DELETE operation takes place.

Triggers help automate important tasks without requiring extra code in the application.

Why Use Triggers?

Triggers are useful for:

• Recording activity logs.
• Preventing invalid data changes.
• Updating related tables automatically.
• Maintaining data consistency.
• Sending notifications in some database systems.

Because triggers execute automatically, they help enforce business rules.

Creating a Simple Trigger

Suppose you want to record every new student added to the database. A simple trigger might look like this:

CREATE TRIGGER StudentAdded
ON Students
AFTER INSERT
AS
PRINT 'A new student has been added.';

Whenever a new record is inserted into the Students table, the trigger runs automatically.

In real applications, triggers usually insert records into log tables or update related information instead of displaying a message.

Types of Triggers

Most RDBMS systems support different types of triggers.

INSERT Trigger

An INSERT trigger runs automatically after a new record is added.

• Record new customer registrations.
• Update inventory after adding stock.

UPDATE Trigger

An UPDATE trigger runs when an existing record is modified.

• Save previous salary details before updating an employee's salary.
• Track changes made to customer information.

DELETE Trigger

A DELETE trigger runs when a record is removed.

• Save deleted records in an archive table.
• Record who deleted the data and when.

These triggers help organizations maintain accurate records and improve data security.

Stored Procedures vs Triggers

Although both are stored inside the database, they have different purposes.

In simple terms, a stored procedure waits for someone to run it, while a trigger reacts automatically when a database event happens.

Best Practices

When working with stored procedures and triggers:

• Use stored procedures for frequently repeated SQL operations.
• Keep procedures simple and well organized.
• Use triggers only when automatic actions are necessary.
• Avoid creating too many triggers, because they can make database behavior harder to understand.
• Test procedures and triggers carefully before using them in production systems.