“Heaven’s Light is Our Guide”
Rajshahi University of Engineering & Technology, Rajshahi
Department of Electrical & Computer Engineering
Course Code : ECE 2216
Course Title : Data Base Systems Sessional
Experiment No. : 08
Experiment Date : 26.11.2024
Submission Date : 30.11.2024
Submitted To- Submitted By-
Md. Omaer Faruq Goni A.S.M Al Mosabbir
Assistant Professor, Roll: 2110011
ECE, RUET
Experiment Name: Study of Triggers and Stored Procedure in SQL
Theory:
A trigger is a stored procedure in a database that automatically invokes whenever a special
event in the database occurs. A trigger is a collection of SQL statements with particular names
that are stored in system memory. It belongs to a specific class of stored procedures that are
automatically invoked in response to database server events. Every trigger has a table attached
to it. SQL triggers are essential tools in database management systems (DBMS) that allow
automatic execution of a set of SQL statements when specific database events, such
as INSERT, UPDATE, or DELETE operations, occur. Triggers are commonly used to
maintain data integrity, track changes, and enforce business rules automatically, without
needing manual input. By using SQL triggers, developers can automate tasks, ensure data
consistency, and keep accurate records of database activities.
An SQL stored procedure is a group of pre-compiled SQL statements (prepared SQL code)
that can be reused by simply calling it whenever needed. It can be used to perform a wide range
of database operations such as inserting, updating, or deleting data, generating reports, and
performing complex calculations. Stored procedures are very useful because they allow you to
encapsulate (bundle) a set of SQL statements as a single unit and execute them repeatedly with
different parameters, making it easy to manage and reuse the code.
Required Apparatus:
1. Computer
2. XAMPP software
3. Internet Browser
Problem: Maintain Product Stock and Audit Log Using Before and After Triggers
Task Description:
Create a system that manages product orders with the following tables:
Products: Stores product details and stock levels.
Orders: Records customer orders.
AuditLog: Logs changes made to orders.
Use:
A BEFORE INSERT trigger to validate stock availability before placing an order.
An AFTER INSERT trigger to log order details in the AuditLog table after a successful order.
Table Creation:
Code:
CREATE TABLE products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Stock INT NOT NULL
);
CREATE TABLE orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
ProductID INT,
Quantity INT,
OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
CREATE TABLE auditlog (
LogID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
Action VARCHAR(50),
LogTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This SQL queries will create tables ‘products’ that will store the product details and stock level,
‘orders’ which will store customer order, ‘auditlog’ will store changes made by orders.
Output:
Fig.-8.1: Created Tables as required
Data Insertion:
Code:
INSERT INTO products (ProductID, ProductName, Stock)
VALUES
(1, 'Laptop', 50),
(2, 'Smartphone', 100),
(3, 'Tablet', 75);
This SQL code will insert values in designated columns in the “products” table.
Output:
Fig.-8.2: Inserted Data
Trigger Creation:
Before Insert Trigger:
Code:
DELIMITER $$
CREATE TRIGGER before_order_insert
BEFORE INSERT ON Orders
FOR EACH ROW
BEGIN
IF (SELECT Stock FROM Products WHERE ProductID = NEW.ProductID) <
NEW.Quantity THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Not enough stock available for this order';
END IF;
END$$
DELIMITER ;
The sql code defines a trigger named before_order_insert on the orders table. This trigger
executes before any new row is inserted into the Orders table. It executes prior to the insertion
and checks the Stock in the Products table for the specified ProductID in the new order. If the
available stock is less than the Quantity in the order, the trigger raises a custom error with the
message "Not enough stock available for this order," using the SIGNAL statement. This
prevents the insertion of the new order, ensuring that orders cannot be placed when stock is
insufficient.
AFTER INSERT Trigger:
Code:
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Products
SET Stock = Stock - NEW.Quantity
WHERE ProductID = NEW.ProductID;
INSERT INTO AuditLog (OrderID, Action)
VALUES (NEW.OrderID, 'Order placed successfully');
END$$
DELIMITER ;
The sql code defines a trigger named after_order_insert on the orders table. The trigger
executes automatically after a new order is successfully inserted into the Orders table. It first
updates the Stock in the Products table by subtracting the Quantity from the available stock for
the specific ProductID in the new order. Then, it records the action in the AuditLog table by
inserting a new entry with the OrderID of the newly placed order and a message indicating
"Order placed successfully." This trigger ensures stock levels are updated and audit logs are
maintained for each new order.
Output:
Fig.-8.3: List of Created Tables
Trigger Test:
Valid Order:
Code:
INSERT INTO Orders (ProductID, Quantity)
VALUES (1, 10);
This code will insert valid data in order table. As a result the AFTER INSERT trigger will
activate.
Output:
Fig.-8.4: Products Table with changed Quantity
Fig.-8.5: Order and AuditLog table signifying Valid Order
Invalid Order:
Code:
INSERT INTO Orders (ProductID, Quantity)
VALUES (1, 100);
The sql code will attempt to insert invalid quantity in orders table. Which will activate
BEFORE INSERT trigger.
Output:
Fig.-8.6:BEFORE INSERT Trigger activation
Problem:
Stored Procedure: PlaceOrder
Input Parameters: Product ID and Quantity
Operations:
Validate stock availability.
Update the stock in the Products table.
Insert the order in the Orders table.
Log the action in the AuditLog table.
Output: Return a success or failure message.
Stored Procedure Creation:
Code:
DELIMITER $$
CREATE PROCEDURE PlaceOrder (
IN p_ProductID INT,
IN p_Quantity INT)
BEGIN
DECLARE available_stock INT;
SELECT Stock INTO available_stock
FROM Products
WHERE ProductID = p_ProductID;
IF available_stock < p_Quantity THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Not enough stock available for this order';
ELSE
UPDATE Products
SET Stock = Stock - p_Quantity
WHERE ProductID = p_ProductID;
INSERT INTO Orders (ProductID, Quantity)
VALUES (p_ProductID, p_Quantity);
INSERT INTO AuditLog (OrderID, Action)
VALUES (LAST_INSERT_ID(), 'Order placed successfully');
END IF;
END$$
DELIMITER ;
The provided code creates a stored procedure, PlaceOrder, which automates the process of
placing an order while validating stock, updating inventory, and logging actions. It retrieves
the stock for the specified p_ProductID from the Products table. If the available stock is less
than p_Quantity, it raises an error with the message "Not enough stock available for this order."
If enough stock is available, it deducts the ordered quantity from the Products table. It inserts
the order into the Orders table and logs the action into the AuditLog table using the order's ID
(LAST_INSERT_ID()).
Output:
Fig.-8.7: Stored Procedure created
Valid Order:
Code:
CALL PlaceOrder(1, 5);
This code will call the PlaceOrder procedure with the ProductID and Quantity as parameters.
Output:
Fig.-8.8: Products Table with changed Quantity
Fig.-8.9:AuditLog and Orders table signifying valid order
Invalid Order:
Code:
CALL PlaceOrder(1, 50);
This code will call the PlaceOrder procedure with the ProductID and Quantity as parameters.
Since the quantity is more than stock, it will return a failure message.
Output:
Fig.8.10: Message signifying invalid order
Discussion:
The experiment focused on implementing a system to manage product stock and log activities
using SQL triggers and a stored procedure, demonstrating how database mechanisms automate
validation, updates, and logging processes. The first step involved creating and populating the
necessary tables: Products to store product details and stock levels, Orders to record customer
orders, and AuditLog to track changes made to orders. Two triggers were developed: a
BEFORE INSERT trigger to validate stock availability before an order is placed and an
AFTER INSERT trigger to update stock and log the successful placement of an order.
Additionally, a stored procedure, PlaceOrder, was implemented to encapsulate the order
placement process, validating stock availability, inserting the order into the Orders table, and
delegating stock updates and logging to the triggers. Testing highlighted the functionality of
both triggers and the procedure; valid orders reduced stock levels and generated logs, while
invalid orders raised errors, enforcing data integrity and automating stock management. This
experiment demonstrated the combined power of triggers and procedures in building efficient,
reliable database systems.
References:
[1] "SQL Trigger | Student Database," GeeksforGeeks, [Online]. Available:
https://www.geeksforgeeks.org/sql-trigger-student-database/. [Accessed: 29-Nov-2024].
[2] "SQL - Stored Procedures," TutorialsPoint, [Online]. Available:
https://www.tutorialspoint.com/sql/sql-stored-procedures.htm. [Accessed: 29-Nov-2024]
